RelationalDBDesign RelationalDBDesign 


Partitioned Tuning   «Prev  Next»
Lesson 6 Partitioned index maintenance
Objective Describe procedures for rebuilding a local partitioned index.

Partial local index Partition Rebuilding

For index partitions to remain effective, they must be properly maintained. There are cases where Oracle performance can be improved by re-building an index using the 
ALTER INDEX REBUILD COMMAND
. The rebuilds can be run concurrently.

Advantages of partial index rebuilding

The capability to rebuild only part of an index is a great performance feature for the following reasons:
  1. Only the index tree that is out-of-balance needs to be rebuilt.
  2. One index partition can be rebuilt while the other partitions continue to service SQL queries.

Do not rebuild indexes unless you have a solid reason to do so.
Rebuilding indexes is generally unnecessary unless an index is corrupt or you want to move an index to different tablespace.

Rebuild Index Partition

The ALTER INDEX REBUILD PARTITION statement can be used to regenerate a single partition in a local or global partitioned index. This saves you from having to perform DROP INDEX and then CREATE INDEX, which would affect all partitions in the index.
ALTER INDEX REBUILD PARTITION has four important applications:
  1. To recluster an index partition to recover space and improve performance.
  2. To repair an index partition in case of a media failure on the volume where the index partition resides or a software corruption of the segment containing the index partition.
  3. To regenerate a local index partition after loading the underlying table partition with Import or SQL*Loader. These utilities offer a performance option to bypass index maintenance, mark the affected index partitions INDEX UNUSABLE, and let the DBA rebuild them later. In other words, the strategy of "drop index then re-create index" can be replaced by a strategy of mark index partition UNUSABLE then rebuild index partition.
  4. To rebuild index partitions rendered UNUSABLE by partition maintenance operations on the underlying table.

Rebuilding selected partitions within an index

There are several advantages to reorganizing selected partitions within an index. This partial index rebuilding technique has been extended to the partitioning environment with the ALTER INDEX REBUILD SUBPARTITION syntax. The SUBPARTITION can only be used with local partitioned indexes. Global partitioned indexes cannot use this feature.

Just like with the ALTER INDEX REBUILD command, it is important to remember to use the TABLESPACE clause, particularly if you need the index to be in the same tablespace.
The SlideShow below reviews the alter_index command used to rebuild an index partition. 

ALTER INDEX DATE_IDX REBUILD PARTITION 2019_IDX
  TABLESPACE TS_2019_IDX
  1. We issue the alter index command to rebuild the partition
  2. The 1998 and 2000 indexes may still service queries while the 1999 index is being built.

Oracle Index Rebuild
Experienced DBAs will double-size their index tablespaces so they can rebuild an index partition in the same tablespace.
The tablespace must have enough room for the index and for the temporary segments in the new index.
The next lesson wraps up this module.