Create Global index on Partitioned Table in Oracle

Creating a global index on a partitioned table in Oracle 12c 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 holistic indexing mechanism across the entire dataset, irrespective of how the underlying table data is partitioned. The syntax for creating a global index on a partitioned table in Oracle 12c is as follows:

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, the syntax would be:

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

In this example, `sales_region_date_idx` is a global index created 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, but rather provides an overarching indexing structure.
It's important to note that while global indexes can be beneficial for certain query patterns, they may also introduce complexities in maintenance operations such as partition maintenance or data loading. Global indexes need to be rebuilt or maintained when certain types of partition maintenance operations are performed on the table. Therefore, the choice between local and global indexing should be based on the specific use case and the nature of the data access patterns and maintenance operations anticipated for the table in question.

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 in the

CREATE INDEX

statement, as in:

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

The MAXVALUE keyword sets an unlimited upper bound for the last partition in this 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. In addition, the syntax for defining a global index is different from defining a local index. You only need to specify that a local index is 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.

Oracle does not support global non-prefixed indexes. You can still have an index that is not prefixed, but it cannot be partitioned.
Oracle also does not support unique non-prefixed local indexes where the partitioning columns in the index are not a subset of the partition columns in the associated table. Using the example from the previous lesson, if a table is partitioned on colA, and the local index is partitioned on colB, colB can not be unique. If colB is unique, you would have to declare the index a global partitioned index.
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