Indexing Partitioned Tables in Oracle Using Equipartitioning

When you index a table, you can partition the index just as you partition the table itself, and this process works because indexes are separate database objects. Partitioning the index generates the same benefits as a partitioned table: improved performance, reduced maintenance time, and increased availability. Many applications use a concept called equipartitioning to increase the value of partitioned tables and indexes together. With equipartitioning, an index has the same partitions as its table: the same number of partitions, the same partitioning columns, and the same partition bounds. You can still use different physical storage attributes even though the partitions themselves match, which lets you store the index and table in different tablespaces if you choose. A partitioned table can have both partitioned and non-partitioned indexes on it.

When you create a partitioned index in Oracle, the index can be aligned with the partitioning strategy of its table, commonly referred to as equipartitioning, or it can define its own independent partitioning. These are two genuinely different syntax forms, not variations of the same clause, so it is worth separating them clearly.

A LOCAL index automatically inherits the partitioning of its table: the same number of partitions, the same partitioning columns, and the same bounds. Because a LOCAL index's partitioning is derived from the table, it takes no PARTITION BY clause of its own:

CREATE INDEX index_name ON table_name (column_list) LOCAL;

A GLOBAL index, by contrast, defines its own partitioning independently of the table, using an explicit PARTITION BY clause:

CREATE INDEX index_name ON table_name (column_list)
  GLOBAL
  PARTITION BY [RANGE | HASH] (partition_key)
      [subpartition_template]
      [partition_spec];

For equipartitioning specifically, you want a LOCAL index. Here is a complete, valid example: a range-partitioned employees table, followed by a LOCAL index that automatically equipartitions with it.

CREATE TABLE employees (
  employee_id   NUMBER,
  department_id NUMBER
)
PARTITION BY RANGE (department_id) (
  PARTITION p1 VALUES LESS THAN (10),
  PARTITION p2 VALUES LESS THAN (20),
  PARTITION p3 VALUES LESS THAN (30),
  PARTITION p4 VALUES LESS THAN (MAXVALUE)
);

CREATE INDEX emp_dept_idx ON employees (department_id) LOCAL;

Because emp_dept_idx is LOCAL, Oracle automatically creates one index partition for each of the four table partitions, using the same boundaries, without you specifying them again. This is what equipartitioning means in practice: the index's structure is a direct reflection of the table's structure, maintained automatically.

This distinction matters beyond just syntax. When you perform partition maintenance on the table, such as ADD PARTITION, SPLIT PARTITION, or DROP PARTITION, a LOCAL index stays in sync automatically: Oracle adjusts the matching index partition as part of the same operation. A GLOBAL index has no such guarantee. Certain partition maintenance operations on the table can leave a GLOBAL index in an UNUSABLE state, requiring an explicit ALTER INDEX ... REBUILD before it can be used again. This is one of the main practical reasons equipartitioned LOCAL indexes are the default choice on partitioned tables, reserving GLOBAL indexes for cases where the index genuinely needs different partitioning than its table, such as enforcing uniqueness on a column that is not the partition key.

As with partitioned tables, Oracle's data dictionary lets you confirm how a partitioned index was actually built. USER_IND_PARTITIONS lists every partition of an index you own, along with its status and tablespace:

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

The STATUS column is worth checking after any partition maintenance operation on the underlying table: a value of UNUSABLE on a GLOBAL index partition is the signal that a rebuild is needed before that index can be used by the optimizer again.

The syntax for creating a partitioned index is very similar to the syntax for creating a partitioned table. You append partitioning keywords to the end of a standard CREATE INDEX statement. However, the syntax is different for each type of partitioned index.

Partitioned indexes are more complicated than partitioned tables because there are four different types:

1. Local prefixed
2. Local non-prefixed
3. Global prefixed
4. Global non-prefixed

In broad terms, a prefixed index has the partitioning key as its leading column, which lets Oracle prune the index the same way it prunes the underlying table; a non-prefixed index does not, which changes how the optimizer can use it during pruning. You will learn more about these different kinds of partitioned indexes in the next two lessons. The next lesson discusses local partitioned indexes.