Defining Local Partitioned Indexes in Oracle

In a local partitioned index, all keys in a particular index partition refer only to rows stored in a single underlying table partition. A local index handles data from exactly one partition — the index is not defined as local unless the LOCAL keyword is specified at CREATE INDEX time, and only one LOCAL clause may appear in a CREATE INDEX statement.

The key structural characteristic of a local partitioned index is that each index partition has its own independent B-tree covering exactly the rows in its corresponding table partition. A master index node makes the separate index trees appear as a single logical index to SQL queries and application code. The following diagrams illustrate the two-level structure:

1. A master index node makes the separate indexes appear as a single index.
2. Each local partition has a separate index tree.

A local partitioned index can only be created on a partitioned table. The table's partitioning strategy dictates how the index partitions are aligned — one index partition per table partition, automatically. The full syntax allows optional per-partition storage customization:

CREATE INDEX index_name ON table_name (column_list)
LOCAL
(
  PARTITION partition_name [TABLESPACE tablespace_name],
  ...
);

• LOCAL — specifies that the index is partitioned on the same lines as the table, with each index partition corresponding to exactly one table partition.
• PARTITION partition_name — names the index partition; conventionally matches the table partition name for clarity.
• TABLESPACE tablespace_name — optional; if omitted, the index partition uses the default tablespace of the corresponding table partition.

For most cases, the partition clause can be omitted entirely. Oracle automatically creates one index partition per table partition:

-- Minimal syntax: one index partition created per table partition automatically
CREATE INDEX sales_amount_idx ON sales (sales_amount)
LOCAL;

After creating the index, verify its structure and partitioning using the data dictionary:

-- Confirm index type and partitioning strategy
SELECT index_name, partitioning_type, locality
FROM   user_part_indexes
WHERE  table_name = 'SALES';

-- List index partitions and their tablespaces
SELECT index_name, partition_name, tablespace_name, status
FROM   user_ind_partitions
WHERE  index_name = 'SALES_AMOUNT_IDX'
ORDER BY partition_position;

To maximize the benefits of partitioning, it makes sense to partition a table and its index identically so that the table partition and the index partition map to the same set of rows. This is called equipartitioning^[1] and is implemented automatically when an index is created with the LOCAL keyword on a partitioned table.

Equipartitioning provides three key benefits:

• Simplified maintenance: operations such as dropping a partition work transparently on both the table partition and its corresponding index partition simultaneously. No separate index partition DDL is required.
• Better query access plans: the optimizer knows the local index is scoped to exactly the rows in each table partition, enabling partition-wise index scans and reducing the number of index partitions probed per query.
• Independent recoverability: a single local index partition can be recovered or rebuilt independently of the rest of the index, without taking the entire index offline.

Equipartitioned local indexes have had a significant impact on Oracle's use in large-scale data warehousing. They allow Oracle to manage very large datasets — hundreds of terabytes — while maintaining predictable partition-level query performance and administration.

For data warehouse and DSS applications, local indexes have several advantages over global indexes. Because each local index partition is independently maintained and scoped to its table partition, partition-level operations are faster, safer, and less disruptive than the equivalent operations on a global index.

A Decision Support System (DSS) query on a very large table presents a specific performance problem. If an ad hoc query requires a full table scan, every record must be inspected — on a table with hundreds of millions of rows, this can take hours. Where the query can be restricted to a single partition or a range of partitions through partition pruning, response time is dramatically reduced. Partition scans rather than full table scans are the core performance mechanism for DSS workloads, and local partitioned indexes enable the optimizer to use partition-wise index access paths efficiently.

A Decision Support System is defined as a system designed to solve semi-structured problems — problems that have both a structured component (quantifiable rules stored as decision logic) and an unstructured component requiring human intuition. DSS workloads differ fundamentally from OLTP workloads: they involve large-volume aggregation and range queries across many rows rather than single-row lookups, making partition-level data access the most effective performance strategy.

The structured components of a DSS decision can be stored as rules in the database and queried efficiently. The unstructured component — human judgment — is left to the user. Examples of semi-structured problems:

1. Choosing a stock portfolio: structured rules define diversification requirements and acceptable risk levels; intuitive judgment governs the final stock selection.
2. Choosing a factory site: structured components include land cost and labor availability; unstructured components include quality of life and strategic positioning.
3. Resource allocation: structured rules define constraints and costs; human judgment resolves competing priorities and organizational tradeoffs.

A practical test for identifying semi-structured problems: do people with the same level of knowledge demonstrate different levels of skill in solving it? If yes, the unstructured component is significant and the system must support human interaction alongside automated rules.

For Oracle data warehouses serving DSS workloads, the combination of range partitioning on the table and equipartitioned local indexes on query-critical columns is the standard architecture. Partition pruning eliminates irrelevant partitions from the query plan; local index partition scans replace full index scans on the pruned partitions, delivering sub-minute response times on queries that would otherwise take hours against an unpartitioned table.

The experienced DBA creates a local partitioned index with the same partition key as the partitioned table. This ensures all index keys in each local index partition point to values within the corresponding table partition — the definition of equipartitioning — and enables the optimizer to use partition-wise index access for both range and point queries without probing partitions that cannot contain qualifying rows.

Local partitioned indexes work in close conjunction with partitioned tables and deliver measurable performance benefits for DSS and data warehouse workloads. Once index partitions are created, they require ongoing maintenance. The next lesson explores index maintenance with partitioned indexes.

[1] Equipartitioning: if you partition a table that has a nested table, Oracle Database uses the partitioning scheme of the original base table as the basis for how the nested table is partitioned. This partitioning of one base table partition for each nested table partition is called equipartitioning. By default, nested tables are automatically partitioned when the base table is partitioned. Composite partitioning is not supported for nested tables.