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 total value of partitioned tables and indexes. With equipartitioning, you have the same partitions for an index as you have for its table, the same number, partitioning columns, and partition bounds. However, you can have different physical storage attributes even though you have the same partitions. This allows you to store the index and table in different tablespaces. A partitioned table can have both partitioned and non-partitioned indexes on it.

In Oracle 12c, when you are working with partitioned tables and need to create a partitioned index, the syntax is focused on ensuring that the index is aligned with the partitioning strategy of the table, commonly referred to as equipartitioning. Equipartitioning ensures that the partitioning of the index matches that of the table, both in terms of the number of partitions and the partitioning key. The general syntax for creating a partitioned index on a partitioned table in Oracle 12c is as follows:

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

• `index_name`: The name you want to assign to the index.
• `table_name`: The name of the partitioned table on which the index is being created.
• `column_list`: The columns to be included in the index.
• `LOCAL | GLOBAL`: Specifies whether the index is partitioned independently of the table (`GLOBAL`) or aligned with the table's partitioning (`LOCAL`). For equipartitioning, you will typically use `LOCAL`.
• `PARTITION BY [RANGE | LIST | HASH] (partition_key)`: Specifies the partitioning strategy, which should match the partitioning strategy of the table. The options are `RANGE`, `LIST`, or `HASH`, and `partition_key` is the column or set of columns used for partitioning.
• `subpartition_template`: Optional definition for subpartitions, if the table uses composite partitioning.
• `partition_spec`: Defines individual partitions and their storage characteristics, which can include partition names and value ranges or lists for range and list partitioned tables, respectively.

Here is an example of creating a local partitioned index on a range-partitioned table:

CREATE INDEX emp_dept_idx ON employees (department_id)
    LOCAL
    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)
    );

In this example, `emp_dept_idx` is an index on the `employees` table, specifically on the `department_id` column. The index is locally partitioned by range, with partitions aligned to the partitions of the `employees` table. Ensure that the partitioning key and strategy of the index match those of the table for true equipartitioning. This alignment is crucial for performance optimization and effective data management in Oracle 12c databases.

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

You will learn more about these different kinds of partitioned indexes in the next two lessons. The next lesson discusses local partitioned indexes.