Create a Global Index on a Partitioned Table in Oracle

Creating a global index on a partitioned table in Oracle 23ai involves defining an index that is independent of the partitioning strategy of the table. A global index can span across all partitions of the table, providing a single indexing structure across the entire dataset, regardless of how the underlying table data is partitioned.
The simplest form of a global index is nonpartitioned. The syntax is:

CREATE INDEX index_name ON table_name (column_list)
    GLOBAL
    [STORAGE parameters]
    [tablespace_clause]
    [other_index_attributes];

• index_name: The name of the index to be created.
• table_name: The name of the partitioned table on which the index will be created.
• column_list: The list of columns to be included in the index.
• GLOBAL: Specifies that the index is a global index, which is not partitioned in line with the table's partitioning scheme.
• STORAGE parameters: Optional storage parameters for the index, such as initial size, next extent size, and maximum size.
• tablespace_clause: Optional specification of the tablespace where the index will be stored.
• other_index_attributes: Other attributes such as logging, parallelism, or compression settings.

For example, to create a global index on a partitioned table named sales based on the sales_region and sales_date columns:

CREATE INDEX sales_region_date_idx ON sales (sales_region, sales_date)
    GLOBAL;

Here, sales_region_date_idx is a global index on the sales table covering the sales_region and sales_date columns. As a global index, it does not conform to the partitioning of the sales table, and instead provides an overarching indexing structure across all of it.

This is exactly the situation a global index is meant for: the sales table might be partitioned by sales_date for maintenance and archiving purposes, but a query that filters primarily by sales_region across all dates would gain little from a local index that mirrors the date-based partitions. A global index on sales_region lets that query go straight to the relevant rows without touching partitioning at all.

Global indexes can be beneficial for query patterns like this one, but they introduce real complexity in maintenance. A global index needs to be rebuilt or maintained when certain partition maintenance operations, such as dropping or truncating a partition, are performed on the table, since Oracle cannot simply drop or truncate the matching index partition the way it can with a local index. The choice between local and global indexing should be based on the specific query patterns and maintenance operations expected for the table.

Unlike local indexes, a global index is not automatically equipartitioned with its underlying table. When you define a global partitioned index, you have to specify the partitions yourself in the CREATE INDEX statement, as in:

CREATE INDEX idxB ON tabA(colB)
GLOBAL PARTITION BY RANGE (colB)
(PARTITION p1 VALUES LESS THAN (10),
 PARTITION p2 VALUES LESS THAN (100),
 PARTITION p3 VALUES LESS THAN (MAXVALUE));

The MAXVALUE keyword sets an unlimited upper bound for the last partition in a definition. You can use MAXVALUE for the last partition in either an index or a table.

A global index has entries that can refer to more than one table partition. Although you could define a global partitioned index with the same partitions and range boundaries as its underlying table, you have to maintain the connection between the index partitions and the table partitions yourself, since Oracle does not track that relationship automatically the way it does for local indexes. The syntax also differs: for a local index, you only need to specify LOCAL and Oracle does the rest. For a global index, you have to explicitly define the partitions and their boundaries, just as you do with a table.

As with local indexes, the dictionary view USER_IND_PARTITIONS shows the current state of each partition in a global partitioned index:

SELECT index_name, partition_name, status
FROM   user_ind_partitions
WHERE  index_name = 'IDXB'
ORDER BY partition_position;

This check matters more for global indexes than local ones: because a global index's partitions are not automatically kept in sync with the table's partitions, a partition maintenance operation on the table is more likely to leave a global index partition UNUSABLE, requiring an explicit ALTER INDEX ... REBUILD PARTITION before that partition can be used again.

Oracle does not support global non-prefixed indexes. You can still have an index that is not prefixed, but it cannot be partitioned.

A related rule applies to local indexes: a unique local index is only valid if the table's partitioning key is included among the index's own columns. Using the example from the previous lesson, if a table is partitioned on colA and a local index is created on colB alone, that index cannot be declared unique, since colA is not part of it. If uniqueness on colB is required, the index must be declared global instead, since a global index carries no such restriction.
The next lesson explains how to merge existing partitions for a table or index.

Click the Quiz link below to answer a few questions about local and global partitions.
Global indexes - Quiz