Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

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.

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.
–
RandyDec 21 '11 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.
–
DCookieDec 21 '11 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.
–
Mark J. BobakDec 21 '11 at 17:43