Indexes on Oracle Partitions

From Oracle9i, we can create indexes on partitions. Like partitioned tables, partitioned indexes will improve SPAM (Scalability, Performance, Availability and Manageability).

The indexes can be

a. Local Partitioned Indexes
Local index is a separate index for each partition.

A local index is an index on a partitioned table which is partitioned in the exact same manner as the underlying partitioned table. Each partition of a local index corresponds to one and only one partition of the underlying table.

CREATE [UNIQUE] INDEX ind-name ON tab-name (ind-col) LOCAL;
or
CREATE [UNIQUE] INDEX ind-name ON tab-name (ind-col) LOCAL
 (PARTITION part-name ... TABLESPACE tbs,
  PARTITION part-name ... TABLESPACE tbs,
  ... ... ... );

A local index can be unique. However, in order for a local index to be unique, the partitioning key of the table must be part of the index's key columns. Unique local indexes are useful for OLTP environments. We cannot explicitly add a partition to a local index. Instead, new partitions are added to local indexes only when we add a partition to the underlying table.

b. Global Partitioned Indexes
Global index is a single index covering all partitions.

A global partitioned index is an index on a partitioned or non partitioned tables which are partitioned using a different partitioning key from the table and can have different number of partitions. Global partitioned indexes can only be partitioned using range partitioning.

CREATE [UNIQUE] INDEX ind-name ON tab-name (ind-col) GLOBAL
PARTITION BY RANGE (col-names)
(
 PARTITION part-name VALUES LESS THAN (value) [TABLESPACE tbs-name],
 PARTITION part-name VALUES LESS THAN (value) [TABLESPACE tbs-name],
 ... ... ... ...
 PARTITION part-name VALUES LESS THAN (MAXVALUE) [TABLESPACE tbs-name]
);

By default, the following operations on partitions on a heap-organized table mark all global indexes as unusable:

• ADD (HASH)
• COALESCE (HASH)
• DROP
• EXCHANGE
• MERGE
• MOVE
• SPLIT
• TRUNCATE

c. Global Non-Partitioned Indexes
A global non-partitioned index is identical to an index on a non-partitioned table. The index structure is not partitioned.

What kind of partitioned index to use:

• If the table partitioning column is a subset of the index keys, use a local index.
• If your priority is manageability, use a local index.
• If the index is unique, use a global index.
• If the application is an OLTP and users need quick response times, use a global index. If the application is a DSS and users are more interested in throughput, use a local index.

Both local and global indexes can be subdivided further:

Prefixed - The partition key is the leftmost column(s) of the index. Probing this type of index is less costly. If a query specifies the partition key in the where clause partition pruning is possible.

Non-Prefixed - Does not support partition pruning, but is effective in accessing data that spans multiple partitions. Often used for indexing a column that is not the tables partition key, when you would like the index to be partitioned on the same key as the underlying table.

Oracle does not support Global Non Prefixed indexes.
Rebuilding Index Partitions
Rebuilding local indexes
SQL> ALTER TABLE ttt MODIFY PARTITION ppp REBUILD UNUSABLE LOCAL INDEXES;

SQL> ALTER TABLE ttt REBUILD PARTITION ppp TABLESPACE tbs-name;

Rebuilding global indexes
Some operations, such as ALTER TABLE DROP PARTITION, mark all Oracle partitions of a global index unusable.

We can rebuild global index partitions in two ways:
 1. Rebuild each partition by issuing the ALTER INDEX REBUILD PARTITION statement (we can run the rebuilds concurrently).
SQL> ALTER INDEX index_name REBUILD PARTITION ind_part_name ONLINE;
 2. Drop the index and re-create it.