What is the difference between an Oracle global index and a local index?
: When using Oracle partitioning, you can specify the 1) global or 2)local parameter in the create index syntax:
- 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.
- 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.