RelationalDBDesign RelationalDBDesign



Partitioned Tables   «Prev  Next»
Lesson 7 Global indexes
Objective Create a global index on a partitioned table.

Create a global index on a partitioned table in Oracle

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.

Prefixed and non-prefixed

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.

Global indexes - Quiz

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