Oracle Tips: Oracle Index Rebuild Command
Oracle Tips by Burleson Consulting
Rebuilding the Unbalanced Indexes
As we see, we have created the list of indexes to be rebuilt,
specifying the tablespace names (Listing 8.9) Please note that
Oracle’s index rebuild command is very fast when compared to
dropping and recreating the index.
IMPORTANT - See my updates notes on
surrounding index rebuilding.
Using internal benchmarks of the
rebuild command, a one gigabyte index was completely rebuilt in only
20 seconds. This is because Oracle reads the existing index in order
to gather the index node information for the new index. After the
index has been read, Oracle will replace the old index tree in the
same spot in the tablespace as the old index. For even faster
execution time, the unrecoverable clause can be used with the
Listing 8.9 The output from rebuilding the indexes
alter index DON.DON_EK rebuild tablespace INDX_1;
alter index DON.DON_FK_ACT rebuild tablespace INDX_1;
. . .
This is an excerpt from "High Performance Data
Warehousing", copyright 1997. To learn more about Oracle, try
"Oracle Tuning: The Definitive Reference", by Donald K. Burleson.
You can buy it direct from the publisher at 30% off here: