Databases Reference
In-Depth Information
partition. A global index is invalidated if the rows of data in a partition are affected by DDL on that
partition. Unlike in the case of a local index, you must rebuild all index partitions of a global index
following a partition maintenance operation such as moving a table partition.
Rebuilding Global Partitioned Indexes
When dealing with global index partitions, the best strategy probably is to drop the index and recreate it
because the database needs to scan the table just once when you do this. The other alternative is to
individually rebuild the global indexes partitions by issuing the
alter index … rebuild
partition
statement. Since you can rebuild multiple partitions simultaneously in parallel, this may not take as
much time as it would if you perform the operation serially.
Rebuilding Local Partitioned Indexes
You can rebuild a local index by issuing either the
alter table
or
alter index
statement. If you want to
rebuild an index regardless of whether the index is marked unusable or not, use the
alter index …
rebuild partition
statement. This statement rebuilds a single partition or a subpartition.
You can't use the
alter index…rebuild
statement to rebuild a composite-partitioned table. You
must instead use the
alter index …rebuild subpartition
statement for any composite-partitioned
tables. Here's an example:
SQL> alter index test1
rebuild subpartition prod_types
tablespace tbs2 parallel (degree 8);
Use the
alter table
statement if you want to rebuild only those indexed partitions and
subpartitions that have been marked unusable
.
Here's the general syntax for the
alter table
statement
to rebuild just the unusable partitions or subpartitions:
alter table … modify partition/subpartition … rebuild unusable local indexes
This
alter table
syntax will rebuild all unusable indexes in a table partition or subpartition.
Specifying the UPDATE INDEXES Clause
You can specify the
update indexes
clause with any of the
alter partition
statements during a partition
maintenance operation, so the database can update the index while it's performing the maintenance
operation on the partitions. This means that you avoid having to rebuild indexes following any
maintenance operations on partitioned tables with indexes. You can specify the
update indexes
clause
for most maintenance operations on partitioned tables. The following partition maintenance operations
mark all global indexes as unusable:
•
Add (for hash partitions) or drop partitions
•
Coalesce (for hash partitions), merge, move partitions
•
Split partitions
•
Truncate partitions
