Local Partitioned Indexes

In a local index, all keys in a particular index partition refer only to rows stored in a single underlying table partition. A local index is created by specifying the LOCAL attribute.
Oracle constructs the local index so that it is equipartitioned with the underlying table. Oracle partitions the index on the same columns as the underlying table, creates the same number of partitions or subpartitions, and gives them the same partition bounds as corresponding partitions of the underlying table.
Oracle also maintains the index partitioning automatically when partitions in the
underlying table are added, dropped, merged, or split, or when hash partitions or
subpartitions are added or coalesced. This ensures that the index remains
equipartitioned with the table.
A local index can be created UNIQUE if the partitioning columns form a subset of the
index columns. This restriction guarantees that rows with identical index keys always
map into the same partition, where uniqueness violations can be detected.
Local indexes have the following advantages:
■ Only one index partition needs to be rebuilt when a maintenance operation other
than SPLIT PARTITION or ADD PARTITION is performed on an underlying table
partition.
■ The duration of a partition maintenance operation remains proportional to
partition size if the partitioned table has only local indexes.
■ Local indexes support partition independence.
■ Local indexes support smooth roll-out of old data and roll-in of new data in
historical tables.
■ Oracle can take advantage of the fact that a local index is equipartitioned with the
underlying table to generate better query access plans.
■ Local indexes simplify the task of tablespace incomplete recovery. In order to
recover a partition or subpartition of a table to a point in time, you must also
recover the corresponding index entries to the same point in time. The only way to
accomplish this is with a local index. Then you can recover the corresponding
table and index partitions or subpartitions together.

Local Prefixed Indexes
A local index is prefixed if it is partitioned on a left prefix of the index columns. For example, if the sales table and its local index sales_ix are partitioned on the
week_num column, then index sales_ix is local prefixed if it is defined on the
columns (week_num, xaction_num). On the other hand, if index sales_ix is
defined on column product_num then it is not prefixed.
Local prefixed indexes can be unique or nonunique.

Following Figure illustrates another example of a local prefixed index.


Local Nonprefixed Indexes
A local index is nonprefixed if it is not partitioned on a left prefix of the index
columns.
You cannot have a unique local nonprefixed index unless the partitioning key is a
subset of the index key.

Posted in: Index,Partitioning