create index before adding columns vs. create index after adding columns - does it matter?

Question

In Oracle 10g, does it matter what order create index and alter table comes in?

Say i have a query Q with a where clause on column C in table T. Now i perform one of the following scenarios:

I create index I(C) and then add columns X,Y,Z.
Add columns X,Y,Z then create index I(C).

Q is 'select * from T where C = whatever'

Between 1 and 2 will there be a significant difference in performance of Q on table T when T contains a very large number of rows?

I personally make it a practice to do #2 but others seem to have a different opinion.

thanks

Justin Cave · Accepted Answer · 2011-08-04 17:07:43Z

4

It makes no difference if you add columns to a table before or after creating an index. The optimizer should pick the same plan for the query and the execution time should be unchanged.

Depending on the physical storage parameters of the table, it is possible that adding the additional columns and populating them with data may force quite a bit of row migration to take place. That row migration will generate changes to the indexes on the table. If the index exists when you are populating the three new columns with data, it is possible that populating the data in X, Y, and Z will take a bit longer because of the additional index maintenance.

answered Aug 4, 2011 at 17:07

Justin Cave

233k25 gold badges378 silver badges395 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Gary Myers · Accepted Answer · 2011-08-05 01:04:13Z

If you add columns without populating them, then it is pretty quick as it is just a metadata change. Adding an index does require the table to be read (or potentially another index) so that can be very time consuming and of much greater impact than the simple metadata change of recording the new index details.

If the new columns are going to be populated as part of the ALTER TABLE, it is a different matter.

The database may undergo an unplanned shutdown during the course of adding that data to every row of the table data
The server memory may not have room to record every row changed in that table
Therefore those row changes may be written to datafiles before commit, and are therefore written as dirty blocks
The next read of those blocks, after the ALTER table has successfully completed will do a delayed block cleanout (ie record the fact that the change has been committed)

If you add the columns (with data) first, then the create index will (probably) read the table and do the added work of the delayed block cleanout.

If you create the index first then add the columns, the create index may be faster but the delayed block cleanout won't happen and that housekeeping will be picked up by the application later (potentially by the select * from T where C = whatever)

Collectives™ on Stack Overflow

create index before adding columns vs. create index after adding columns - does it matter?

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related