Local Indexes Partitioned Oracle Table

When you create a local index, it is automatically equipartitioned^[1] in the same way as its underlying table. This gives you the advantages of an equipartitioned index without declaring the index partitions when the index is created or when the table partitions are maintained or modified. The following diagram illustrates the relationship between a local index and its corresponding table.

Local Index A	Each index partition mirrors a table partition.
Table A	You set up the table partitions when you define or maintain the table.

The most common type of partitioned index is the locally partitioned index. Locally partitioned indexes can only be created on partitioned tables. As specified by the name, “local” means there is a direct relationship between entries for an index and the corresponding data. There is a one-to-one relationship between data partitions and index partitions. If you have a table partitioned by range based on dates and you have a partition for every month of the year, then for all the data for the January 2012 partition, you have, for each index created, the corresponding index entries in the January 2012 index partition(s). See Figure 2-6 for an example of the architecture between data and index partitions for a locally partitioned index.

Question:What is the difference between an Oracle global index and a local index?
Answer: When using Oracle partitioning, you can specify the 1) global or 2)local parameter in the create index syntax:

1. Global Index: A global index is a one-to-many relationship, allowing one index partition to map to many table partitions. A global index can be partitioned by the range or hash method, and it can be defined on any type of a) partitioned or b) non-partitioned table.
2. Local Index: A local index is a one-to-one mapping between an index partition and a table partition. In general, local indexes allow for a cleaner approach (See Figure 1 below) for generating fast SQL execution plans with partition pruning.

The only attribute you can specify for a hash-partitioned global index or for an index on a hash-partitioned table is TABLESPACE.

1. modify_index_partition: Use the modify_index_partition clause to modify the real physical attributes, logging attribute, or storage characteristics of index partition partition or its subpartitions. For a hash-partitioned global index, the only subclause of this clause you can specify is UNUSABLE.
2. drop_index_partition: Use the drop_index_partition clause to remove a partition and the data in it from a partitioned global index. When you drop a partition of a global index, Oracle Database marks the next index partition UNUSABLE. You cannot drop the highest partition of a global index.
3. split_index_partition: Use the split_index_partition clause to split a partition of a global range-partitioned index into two partitions, adding a new partition to the index. This clause is not valid for hash-partitioned global indexes. Instead, use the add_hash_index_partition clause.
4. coalesce_index_partition: This clause is valid only for hash-partitioned global indexes. Oracle Database reduces by one the number of index partitions. Oracle Database selects the partition to coalesce based on the requirements of the hash function. Use this clause if you want to distribute index entries of a selected partition into one of the remaining partitions and then remove the selected partition.

In order to define a local partitioned index, you only have to add the keyword LOCAL to the end of the CREATE INDEX statement, as in

CREATE INDEX idxA ON tabA(colA) LOCAL ();

The local index will automatically be partitioned in the same way as the underlying table. You can specify which tablespace a local index's partitions will be stored in.

A prefixed index includes the columns used to partition the underlying table at the beginning of the column list for the index, the left of the column list. Any local index that does not include these columns is a non-prefixed partitioned index. Both prefixed and non-prefixed local indexes are partitioned on the underlying column(s) used to partition the associated table. For this example, assume that your underlying table contains three columns:colA, colB, and colC. The table is partitioned on colA. A local prefixed index could contain colA and colB, and it would be partitioned on colA. A local non-prefixed index may only contain colB, but it would still be partitioned on colA.

There are advantages to using each type of index. When you have a local index that is prefixed, the Oracle optimizer will automatically know to eliminate both index partitions and table partitions if the partitioning key is a part of the WHERE clause. This matching provides the fastest query execution. If an index is not prefixed, you can specify a different column and still benefit from the improved performance. For instance, you could have a table that is partitioned by month and a non-prefixed local index that is partitioned by account number. Assuming that a query asked for the sales for a particular month for a particular account, Oracle could use the index to quickly locate the account number but would not have to search table partitions that did not contain the correct month. The next lesson explains global partitioned indexes.

[1] equipartitioning: The partitioning of one base table partition for each nested table partition is called equipartitioning.