Lesson 6 | Partitioned index maintenance |
Objective | Describe procedures for rebuilding a local partitioned index. |
Rebuild 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:
- Only the index tree that is out-of-balance needs to be rebuilt.
- 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:
- To recluster an index partition to recover space and improve performance.
- 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.
- 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.
- 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
- We issue the alter index command to rebuild the partition
- 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.