Partitioned Tables   «Prev 

Oracle Global Index versus Local 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.

Figure 1: A local index is a one-to-one mapping between an index partition and a table partition

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.

Restriction on Modifying Partition Default Attributes

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.