Oracle Global Index

A global index is harder to maintain for two reasons. When data in an underlying table partition is moved or removed, all partitions of the global index are affected.

1. A global index requires partition maintenance in relation to table size, not partition size, and does not support independent partitions.
2. An underlying table partition can only be recovered by rebuilding the entire global index.

When an underlying table partition is recovered, all related global index entries must also be recovered. Because these records may be scattered across all partitions of the index, with other records for other partitions that are not being recovered, this can only be achieved by recreating the entire global index.

Global Index: A global index is a one-to-many relationship, allowing one index partition to map to many table partitions. The documentation says that a "global index can be partitioned by the range or hash method, and it can be defined on any type of partitioned, or non-partitioned, table".

The following statement creates a global prefixed index cost_ix on the sample table sh.sales with three partitions that divide the range of costs into three groups:

CREATE INDEX cost_ix ON sales (amount_sold)
GLOBAL PARTITION BY RANGE (amount_sold)
(PARTITION p1 VALUES LESS THAN (1000),
PARTITION p2 VALUES LESS THAN (2500),
PARTITION p3 VALUES LESS THAN (MAXVALUE));