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.

Table Partitions

Can be reorganized faster than the whole table
Can be segregated into separate tablespaces independently of the other partitions
Can be made read only, independently of other table partitions. This is a great feature for a data warehouse.

Question: What Are Index-Organized Tables?

An index-organized table, in contrast to an ordinary table, has its own way of structuring, storing, and indexing data. If you compare this concept to an ordinary table it may help to explain its uniqueness.

Index-Organized Tables Versus Ordinary Tables

A row in an ordinary table has a stable physical location. Once this location is established, the row never completely moves. Even if it is partially moved with the addition of new data, there is always a row piece at the original physical address which is identified by the original physical rowid from which the system can find the rest of the row. As long as the row exists, its physical rowid does not change. An index in an ordinary table stores both the column data and the rowid.
A row in an index-organized table does not have a stable physical location. It keeps data in sorted order, in the leaves of a B*-tree index built on the table's primary key. These rows can move around to preserve the sorted order. For example, an insertion can cause an existing row to move to a different slot, or even to a different block.
The leaves of the B*-tree index hold the primary key and the actual row data. Changes to the table data. For example, adding new rows, or updating or deleting existing rows, result only in updating the 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.

Storing partitions in separate tablespaces

Oracle recommends, but does not require, that you store each index partition in a separate tablespace for the following reasons:

Independent backup and recovery of each partition
Reduced potential for data corruption
Control of partition mapping to disk drive, which affects I/O load balancing

Partitioning Option

Oracle introduced partitioning as an option with Oracle8 to provide a higher degree of manageability and availability. You can take individual partitions offline for maintenance while other partitions remain available for user access. In data warehousing implementations, partitioning is sometimes used to implement rolling windows based on date ranges. Other partitioning types include hash partitioning (used to divide data into partitions using a hashing function and providing an even distribution of data) and list partitioning (enabling partitioning of data based on discrete values such as geography). Starting with Oracle Database 11g, interval partitioning can also be used to automatically create new fixed ranges as needed during data insertions. Many of these partitioning types can be used in combination as composite partitions. Examples of composite partitions in Oracle Database 11g include range-range, range-hash, range-list, list-range, list-hash, and list-list.

When to Use Hash Partitioning

There are times when it is not obvious in which partition data should reside, although the partitioning key can be identified. Rather than group similar data, there are times when it is desirable to distribute data such that it does not correspond to a business or a logical view of the data, as it does in range partitioning. With hash partitioning, a row is placed into a partition based on the result of passing the partitioning key into a hashing algorithm. Using this approach, data is randomly distributed across the partitions rather than grouped. This is a good approach for some data, but may not be an effective way to manage historical data. However, hash partitions share some performance characteristics with range partitions. For example, partition pruning is limited to equality predicates. You can also use partition-wise joins, parallel index access, and parallel DML.

As a general rule, use hash partitioning for the following purposes:

To enable partial or full parallel partition-wise joins with likely equisized partitions.
To distribute data evenly among the nodes of an MPP platform that uses Oracle Real Application Clusters. Consequently, you can minimize interconnect traffic when processing internode parallel statements.
To use partition pruning and partition-wise joins according to a partitioning key that is mostly constrained by a distinct value or value list.
To randomly distribute data to avoid I/O bottlenecks if you do not use a storage management technique that stripes and mirrors across all available devices.

Consider Costs and Benefits of Coalescing or Rebuilding Indexes

Improper sizing or increased growth can produce index fragmentation. To eliminate or reduce fragmentation, you can rebuild or coalesce the index. But before you perform either task weigh the costs and benefits of each option and choose the one that works best for your situation.
Table 5-6 is a comparison of the costs and benefits associated with rebuilding and coalescing indexes

Table 5-6 Costs and Benefits of Coalescing or Rebuilding Indexes

The series of images below reviews the alter_index command used to rebuild an index partition.

Oracle index rebuild

We issue the alter index command to rebuild the partition

ALTER INDEX DATE_IDX REBUILD PARTITION 2009_IDX TABLESPACE TS_2009_IDX
The 1998 and 2000 indexes may still service queries while the 1999 index is being built.

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.

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.