I have a table with a lot of records (more than 15M) and i am executing an alter query to add 2 columns to the table.
the query is taking around 1hr to finish executing.
So my question is: How does an alter work? and why is it affected by the number of records?
is it possible to speed up an alter query using column indexes?

  • 1
    Please, always specify Oracle version, edition, and platform. Commented Dec 21, 2011 at 17:49

2 Answers 2


An ALTER TABLE operation that adds a column to a table should be a simple data dictionary only update. There is no reason for Oracle to visit all the blocks/rows in the table. So, the operation should be very fast. (sub-second response time.)

The only exception to this would be, if you're on a version of Oracle prior to 11g, and you're setting a default value for the column being added, Oracle has to visit all the rows. In 11g, even setting a default value will not precipitate a visit to all the rows in the table.

  • 1
    although you down-voted my question. your answer was the best :)
    – scatman
    Commented Dec 21, 2011 at 18:10
  • 1
    Sorry, but I'm on a crusade to get people who post here to always include Oracle version, edition and platform info. It seems that it's almost always omitted. :-) Commented Dec 21, 2011 at 18:54

Every row in the table needs to be updated to add the new columns if you're specifying a default value. That's why it's affected by the number of records. Column indexes won't help, because indexes aren't involved in this operation.

  • this might be affected by the original PCTFREE and PCTUSED settings so that the new values will or will not create row chaining. In either case, you will probably want to export the results, and reimport to clean things up when you are done.
    – Randy
    Commented Dec 21, 2011 at 16:55
  • Even if no row chaining occurs, the bottom line is that at a minimum, every row needs modification to add the new columns. But yes, row chaining would make the effect worse.
    – DCookie
    Commented Dec 21, 2011 at 17:00
  • 2
    Sorry, but this is just plain wrong. In Oracle, an ALTER TABLE that adds a column is a data dictionary only operation, and should finish in a matter of seconds (or less). The only reason for it to run longer, and to be affected by the number of rows in the table, is if you're declaring a default value on the column. And even then, starting with Oracle 11g, even with a default value declared, it's only a data dictionary operation. Commented Dec 21, 2011 at 17:43
  • Thanks for the correction, Mark. I've changed my answer to reflect this.
    – DCookie
    Commented Dec 21, 2011 at 18:24

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.