Tell me more ×
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.
up vote 1 down vote favorite

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?

share|improve this question
1  
Please, always specify Oracle version, edition, and platform. – Mark J. Bobak Dec 21 '11 at 17:49

migrated from stackoverflow.com Dec 21 '11 at 21:14

2 Answers

up vote 5 down vote accepted

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.

share|improve this answer
1  
although you down-voted my question. your answer was the best :) – scatman Dec 21 '11 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. :-) – Mark J. Bobak Dec 21 '11 at 18:54
up vote 4 down vote

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.

share|improve this answer
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 Dec 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. – DCookie Dec 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. Bobak Dec 21 '11 at 17:43
Thanks for the correction, Mark. I've changed my answer to reflect this. – DCookie Dec 21 '11 at 18:24

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.