Databases Reference
In-Depth Information
Rebuilding Globally Partitioned and Non-Partitioned Indexes
Almost any partition-level operation on a table will render any globally partitioned or non-partitioned
index unusable. Essentially, the indexes always must be rebuilt. One built-in feature in Oracle 11g is to
allow you to rebuild the indexes as part of the partition-level table operation. Using the partition-level
merge operation example in the previous “Merge Partition” section, you can see that you can add the
UPDATE INDEXES
clause as part of the
ALTER TABLE...MERGE
command. This instructs Oracle to rebuild any
indexes marked unusable by the partition-level operation. See the following example:
SQL> ALTER TABLE employees_parttest merge PARTITIONS p1995 , pmax
2 INTO PARTITION pmax
3 UPDATE INDEXES;
Table altered.
Using the same query to see index partition status information, you can see that the globally
partitioned index and the non-partitioned index are now usable, even after the merge operation.
INDEX_NAME NULL STATUS
------------------------------ ------------------------------ --------
EMPLOYEES_PARTTEST_GI1 MANAGER_100 USABLE
EMPLOYEES_PARTTEST_GI1 MANAGER_500 USABLE
EMPLOYEES_PARTTEST_GI1 MANAGER_900 USABLE
EMPLOYEES_PARTTEST_GI1 MANAGER_MAX USABLE
EMPLOYEES_PARTTEST_I1 VALID
EMPLOYEES_PART_LI1 PI1990 USABLE
EMPLOYEES_PART_LI1 PIMAX USABLE
A key advantage to using the
UPDATE INDEXES
clause when rebuilding an index is that it remains
online and available during the rebuild operation. While the aforementioned example is extremely
simple, one drawback of using the
UPDATE INDEXES
clause is that by packaging the partition-level
operation with the index rebuild operation, you lose some flexibility in how you rebuild your indexes.
For instance, if you have multiple indexes to rebuild, it may be faster to issue each index rebuild
separately. By doing this, you can run multiple
ALTER INDEX...REBUILD
commands concurrently. This is
a more complex method, but it may be necessary simply for speed.
For non-partitioned indexes, you simply need to issue an
ALTER INDEX...REBUILD
command.
SQL> ALTER INDEX EMPLOYEES_PARTTEST_I1 REBUILD;
Index altered.
Also, for each index, you can optionally decide to use parallelism, as shown in the following
example:
SQL> alter index EMPLOYEES_PARTTEST_I1 rebuild parallel(degree 4);
Index altered.
