When you create a local index, it is automatically equipartitioned[1] with the same partitioning scheme as its underlying table. This provides the benefits of an equipartitioned index without needing to manually define index partitions during creation or maintenance.
The following diagram illustrates the one-to-one relationship between a local index and its corresponding table.
Figure 1: A local index maps each index partition to a table partition in a one-to-one relationship.
Local Index A
Each index partition corresponds directly to a table partition.
Table A
Table partitions are defined when creating or maintaining the table.
Creating a Locally Partitioned Index
The most common type of partitioned index is the locally partitioned index, which can only be created on partitioned tables. A local index maintains a one-to-one relationship between data partitions and index partitions. For example, if a table is partitioned by range based on dates, with a partition for each month of the year, a local index on that table will have corresponding index partitions for each month. For the January 2012 table partition, all index entries are stored in the January 2012 index partition. See Figure 2-6 for an example of this architecture.
Figure 2-6: Locally partitioned index architecture
This image illustrates a partitioned table and its associated local index in an Oracle database.
Overview of the Structure
Table Name: EMPLOYEES
The table is partitioned based on the HIRE_DATE column.
Each partition stores rows for a specific range of HIRE_DATE values.
Table Partitions:
Partition P80_89:
Stores employees hired between 1980 and 1989.
Contains three rows with the following data:
EMP_ID: 100, 200, 101
LAST_NAME: King, Whalen, Kochhar
HIRE_DATE: 1987-06-17, 1987-09-17, 1989-09-21
Partition P90_99:
Stores employees hired between 1990 and 1999.
Contains three rows with the following data:
EMP_ID: 104, 204, 115
LAST_NAME: Ernst, Baer, Khoo
HIRE_DATE: 1991-05-11, 1994-06-07, 1995-05-18
Index Name: EMPLOYEES_HIRE_DATE_I1
The index is partitioned to match the table’s partitions.
Each index partition stores ROWIDs for rows in the corresponding table partition.
Index Partitions:
Index Partition P80_89:
Maps to the P80_89 table partition.
Contains ROWIDs for rows with HIRE_DATE values from 1980-1989.
Index Partition P90_99:
Maps to the P90_99 table partition.
Contains ROWIDs for rows with HIRE_DATE values from 1990-1999.
Key Features of the Design
Partitioning Strategy:
The table is partitioned by range on the HIRE_DATE column, dividing data into date-based partitions.
Each partition acts as a logical subset, improving manageability and query performance.
Index Partitioning:
The index is local, with each index partition corresponding to one table partition.
This ensures efficient indexing and reduces overhead for partition-specific queries.
Data Distribution:
Employees are grouped by hire year, enabling quick access to rows for specific date ranges.
Query Optimization:
Partitioning enables partition pruning, where Oracle limits queries to relevant partitions, improving performance.
Use Cases
Query Performance:
Queries like SELECT * FROM EMPLOYEES WHERE HIRE_DATE BETWEEN '1987-01-01' AND '1989-12-31' access only the P80_89 partition.
Maintenance:
Partition-level operations (e.g., archiving or dropping old data) simplify maintenance.
Scalability:
Partitioning supports manageability as data volume grows.
This structure demonstrates an effective partitioned table and local index setup in Oracle, optimizing large dataset management.
Oracle Global Index versus Local Index
Question: What is the difference between an Oracle global index and a local index? Answer: In Oracle partitioning, indexes can be defined as global or local:
Global Index: A global index has a one-to-many relationship, where one index partition can map to multiple table partitions. It can be partitioned by range or hash and used on both partitioned and non-partitioned tables.
Local Index: A local index has a one-to-one mapping between an index partition and a table partition. Local indexes simplify partition pruning, leading to faster query execution plans (see Figure 1).
Maintaining Local Index Partitions
Local indexes are automatically maintained when table partitions are modified (e.g., split, merged, or dropped), but you can perform specific maintenance tasks on local index partitions:
Modify Index Partition: Use the ALTER INDEX ... MODIFY PARTITION clause to change physical attributes (e.g., tablespace) or mark a partition as UNUSABLE. For example:
ALTER INDEX idxA MODIFY PARTITION P80_89 TABLESPACE new_tablespace;
Rebuild Index Partition: Rebuild an unusable index partition to restore it:
ALTER INDEX idxA REBUILD PARTITION P80_89;
Specify Tablespace during Creation: Assign a tablespace to local index partitions during creation:
CREATE INDEX idxA ON tabA(colA) LOCAL
(PARTITION P80_89 TABLESPACE ts1, PARTITION P90_99 TABLESPACE ts2);
These operations ensure local indexes remain aligned with table partitions while optimizing storage and performance.
Define a Local Partitioned Index
To define a local partitioned index, add the LOCAL keyword to the CREATE INDEX statement:
CREATE INDEX idxA ON tabA(colA) LOCAL;
The local index automatically adopts the table’s partitioning scheme. You can optionally specify tablespaces for individual index partitions.
Prefixed and Non-Prefixed Indexes
A prefixed local index includes the table’s partitioning key (e.g., colA) at the start of its column list. A non-prefixed local index excludes the partitioning key or lists it after other columns. Both types are partitioned on the table’s partitioning key. For example, if a table is partitioned on colA (e.g., by month) and has columns colA, colB, and colC:
A prefixed index on colA, colB is partitioned by colA.
A non-prefixed index on colB is still partitioned by colA.
Benefits of Prefixed vs. Non-Prefixed Indexes
Prefixed and non-prefixed local indexes offer distinct advantages:
Prefixed Indexes: When the partitioning key (e.g., HIRE_DATE) is in the WHERE clause, the Oracle optimizer uses partition pruning to access only relevant table and index partitions, optimizing query performance.
Non-Prefixed Indexes: These index different columns (e.g., account_number) but are still partitioned by the table’s key (e.g., month). For a query filtering by both month and account number, Oracle prunes partitions by month and uses the index to locate the account number within those partitions, improving performance without scanning irrelevant data.
Example: For a table partitioned by month on HIRE_DATE with a non-prefixed index on account_number, a query like:
SELECT * FROM EMPLOYEES WHERE HIRE_DATE BETWEEN '1990-01-01' AND '1990-12-31' AND account_number = '12345';
prunes to the 1990 partition and uses the index to find the account number efficiently.
In the next lesson, you will explore global partitioned indexes.
[1] equipartitioning: The partitioning of one base table partition for each nested table partition is called equipartitioning.
