Last updated on August 16th, 2015 at 03:19 am
For local indexes, the index keys within the index will refer only to the rows stored in the single underlying table partition. A local index is created by specifying the LOCAL attribute, and can be created as UNIQUE or NONUNIQUE. The table and local index are either partitioned in exactly the same manner, or have the same partition key because the local indexes are automatically maintained, can offer higher availability. As the Oracle database ensures that the index partitions are synchronized with their corresponding table partitions, it follows that the database automatically maintains the index partition whenever any maintenance operation is performed on the underlying tables: for example, when partitions are added, dropped, or merged.A local index is prefixed if the partition key of the table and the index key are the same; otherwise it is a local non-prefixed index
Global Partitioned Indexes: A global partitioned index is an index on a partitioned or non-partitioned table that is partitioned independently, i.e. using a different partitioning key from the table. Global-partitioned indexes can be range or hash partitioned.
Global partitioned indexes are more difficult to maintain than local indexes. However, they do offer a more efficient access method to any individual record. During table or index interaction during partition maintenance, all partitions in a global index will be affected. When the underlying table partition has any SPLIT, MOVE, DROP, or TRUNCATE maintenance operations performed on it, both global indexes and global partitioned indexes will be marked as unusable. It therefore follows that partition independence it is not possible for global indexes.
Depending on the type of operation performed on a table partition, the indexes on the table will be affected. When altering a table partition, the UPDATE INDEXES clause can be specified. This automatically maintains the affected global indexes and partitions. The advantages of using this option are that the index will remain online and available throughout the operation, and does not have to be rebuilt once the operation has completed.
Global Nonpartitioned Indexes
Global nonpartitioned indexes offer the same efficient access to any individual record in any partition, and behave just like a non-partitioned index. Since the index structure is not partitioned, the index is available to all partitions. A scenario where this type of index would be useful is with a query that does not include the partition key of the table as a filter, but you still want the optimizer to use an index