loading Inactive index vs active index

[ctoman]

OE: 10.2B08 however 32-bit
OS: Ubuntu
Table record count over 18 million.
My question is (of course I will test for how long) what is best practice.
1. load inactive index and rebuild or 2. load active index?

Thanks in advance!

 

[Rob Fitzpatrick]

I assume you are referring to the latter half of a binary dump and load. I would load the data with the indexes inactive. Test both offline and online to see which works best for you given your hardware and data and your constraints on downtime. Then take a full offline backup, then rebuild the indexes for the table(s) that you loaded. Be sure to use the newer idxbuild parameters that were added in 10.2B06 (which are not in the 10.2B Database Administration manual).

 

[ctoman]

I assume you are referring to the latter half of a binary dump and load. I would load the data with the indexes inactive. Test both offline and online to see which works best for you given your hardware and data and your constraints on downtime. Then take a full offline backup, then rebuild the indexes for the table(s) that you loaded. Be sure to use the newer idxbuild parameters that were added in 10.2B06 (which are not in the 10.2B Database Administration manual).

sorry i was not clear, just adding an index, no dump and load.

 

[Rob Fitzpatrick]

sorry i was not clear, just adding an index, no dump and load.

My preference is to add indexes with the INACTIVE keyword in the .df and then build or activate them after the fact. If you add them active, the Data Dictionary needs to reads all the records and build the index keys during the schema change. If your table is very large, this can be a slow process.