Defining a Global Partitioned Index

Oracle provides two types of indexes for partitioned tables: global and local. Understanding the structural difference between them and the performance and maintenance implications of each, is essential for tuning partitioned schemas effectively.

In a global partitioned index, a single B-tree structure indexes all rows across the entire partitioned table. The global index organizes data that resides in more than one partition. From the optimizer's perspective, a global partitioned index behaves similarly to an index on a non-partitioned table: there is one index tree, and a single traversal from root to leaf node resolves any lookup regardless of which table partition holds the target row.

A local partitioned index takes the opposite approach. Each table partition has its own dedicated index partition, and Oracle automatically links the index partitioning to the table's partitioning method. The two approaches serve different workloads, and the choice between them is driven by access pattern, partition pruning requirements, and maintenance tolerance.

The following statement creates a global prefixed index cost_ix on the sales table with three partitions that divide the range of amount_sold values 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)
);

Key points about this DDL:

• The index is partitioned by amount_sold — the same column as the index key — making it a prefixed global index. A global index is prefixed when the partition key is a left prefix of the index key columns.
• The index partitioning is independent of the underlying table's partitioning. The table may be partitioned by year while this global index is partitioned by sales amount — the two partition schemes are unrelated.
• MAXVALUE in the final partition ensures all rows are covered regardless of the maximum value of amount_sold.

A partitioned index is an index that, like a partitioned table, has been divided into smaller and more manageable pieces. Global indexes are partitioned independently of the table on which they are created. Local indexes are automatically linked to the table's partitioning method — each table partition has exactly one corresponding local index partition.

Like partitioned tables, partitioned indexes improve manageability, availability, performance, and scalability. The following diagram shows the index partitioning options available for a partitioned table:

Because a global partitioned index has only a single index tree, index partition probes are minimized. For a lookup on any indexed value, Oracle traverses one B-tree from root to leaf and retrieves the ROWID — regardless of which table partition holds the target row. This makes global indexes the preferred choice for high-concurrency OLTP applications where single-row or small-range lookups dominate.

Global indexes also support queries that do not filter on the table's partition key. A query against the sales table that filters on amount_sold but not on the partition key year can use the global cost_ix index efficiently. A local index on amount_sold in this case would require Oracle to probe every local index partition — one per table partition — to satisfy the query.

For data warehouse and Decision Support System (DSS) workloads where queries typically filter on the partition key and partition pruning is the primary performance mechanism, local partitioned indexes are a better choice. Local indexes support independent partition maintenance and align naturally with partition-wise joins and parallel partition scans.

A global partitioned index is harder to maintain than a local index for two structural reasons:

1. Partition-scope maintenance: a global index requires maintenance relative to the size of the entire table, not an individual partition. When data in an underlying table partition is moved, exchanged, split, merged, or dropped, all partitions of the global index are potentially affected. A local index would require only the corresponding index partition to be rebuilt.
2. Recovery scope: when an underlying table partition is recovered, all related global index entries must also be recovered. Because those entries may be scattered across all partitions of the global index alongside entries for other table partitions, recovery can only be achieved by rebuilding the entire global index.

When performing partition DDL on a table with a global index, include the UPDATE GLOBAL INDEXES clause to keep the global index valid without a full rebuild:

-- Drop a table partition and update the global index online
ALTER TABLE sales_data
DROP PARTITION p2022
UPDATE GLOBAL INDEXES;

Without UPDATE GLOBAL INDEXES, the global index is marked UNUSABLE after the partition DDL completes and must be rebuilt with ALTER INDEX ... REBUILD before it can serve queries. This is one of the most common causes of unplanned query failures in partition-heavy schemas. The UPDATE GLOBAL INDEXES clause performs the index maintenance online during the DDL operation itself, at a higher cost per operation but without leaving the index in an unusable state.

A global index is a one-to-many relationship between the index structure and the table partitions — one index partition can map to rows in many table partitions. A global index can be partitioned by range or hash, and can be defined on any type of partitioned or non-partitioned table. The index partitioning strategy is entirely independent of the underlying table's partitioning strategy.

The tradeoff is maintenance complexity: global indexes deliver fewer index partition probes and better support for non-partition-key queries, but require full-index maintenance whenever partition DDL is performed on the underlying table. Local partitioned indexes avoid this maintenance burden by scoping each index partition to its corresponding table partition, at the cost of additional probes for queries that do not filter on the partition key.

The next lesson examines the second index partitioning method: the local partitioned index. Detailed maintenance procedures for partitioned indexes are covered later in the module.