When you create a local index, it is automatically equipartitioned[1] in the same way as its underlying table. This gives you the advantages of an equipartitioned index without declaring the index partitions when the index is created or when the table partitions are maintained or modified.
The following diagram illustrates the relationship between a local index and its corresponding table.
Figure 1: A local index is a one-to-one mapping between an index partition and a table partition
Local Index A
Each index partition mirrors a table partition.
Table A
You set up the table partitions when you define or maintain the table.
Creating Locally Partitioned Index
The most common type of partitioned index is the locally partitioned index. Locally partitioned indexes can only be created on partitioned tables. As specified by the name, “local” means there is a direct relationship between entries for an index and the corresponding data. There is a one-to-one relationship between data partitions and index partitions. If you have a table partitioned by range based on dates and you have a partition for every month of the year, then for all the data for the January 2012 partition, you have, for each index created, the corresponding index entries in the January 2012 index partition(s). See Figure 2-6 for an example of the architecture between data and index partitions for a locally partitioned
index.
This image provides an illustration of a partitioned table and its associated index in a database system. Below is an analysis of the content based on the image:
Overview of the Structure
Table Name: EMPLOYEES
The table is partitioned based on the HIRE_DATE column.
Each partition stores a subset of rows corresponding to a specific range of HIRE_DATE values.
Table Partitions:
Partition P80_89:
Stores employees hired between the years 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 the years 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, matching the partitions of the table.
Each partition of the index stores ROWIDs corresponding to the rows in the associated table partition.
Index Partitions:
Index Partition P80_89:
Maps to the P80_89 table partition.
Contains ROWIDs for rows with HIRE_DATE values in the range 1980-1989.
Index Partition P90_99:
Maps to the P90_99 table partition.
Contains ROWIDs for rows with HIRE_DATE values in the range 1990-1999.
Key Features of the Design
Partitioning Strategy:
The table is partitioned by range on the HIRE_DATE column, dividing data into partitions based on date ranges.
Each partition acts as a logical subset of the table, improving manageability and query performance.
Index Partitioning:
The index is local to the table partitions, meaning each index partition corresponds to a single table partition.
This design ensures efficient indexing and reduces overhead during partition-specific queries.
Data Distribution:
Employees are grouped into partitions based on their hiring years.
This distribution enables quick access to rows for queries targeting specific date ranges.
Query Optimization:
Partitioning enhances query performance by allowing Oracle to perform partition pruning, limiting the scope of queries to relevant partitions.
Use Cases
Query Performance:
Queries such as SELECT * FROM EMPLOYEES WHERE HIRE_DATE BETWEEN '1987-01-01' AND '1989-12-31' will access only the P80_89 partition, skipping irrelevant partitions.
Maintenance:
Maintenance tasks (e.g., data archiving or deleting old records) can be performed at the partition level, simplifying operations.
Scalability:
Partitioning ensures the table remains manageable even as the volume of data grows over time.
This structure demonstrates a well-designed partitioned table and index setup in a relational database, commonly seen in Oracle environments, where partitioning is used to manage and optimize large datasets.
Figure 2-6: Locally partitioned index architecture
Oracle Global Index versus Local Index
Question:What is the difference between an Oracle global index and a local index? Answer: When using Oracle partitioning, you can specify the 1) global or 2)local parameter in the create index syntax:
Global Index: A global index is a one-to-many relationship, allowing one index partition to map to many table partitions. A global index can be partitioned by the range or hash method, and it can be defined on any type of a) partitioned or b) non-partitioned table.
Local Index: A local index is a one-to-one mapping between an index partition and a table partition. In general, local indexes allow for a cleaner approach (See Figure 1 below) for generating fast SQL execution plans with partition pruning.
Restriction on Modifying Partition Default Attributes
The only attribute you can specify for a hash-partitioned global index or for an index on a hash-partitioned table is TABLESPACE.
modify_index_partition: Use the modify_index_partition clause to modify the real physical attributes, logging attribute, or storage characteristics of index partition partition or its subpartitions. For a hash-partitioned global index, the only subclause of this clause you can specify is UNUSABLE.
drop_index_partition: Use the drop_index_partition clause to remove a partition and the data in it from a partitioned global index. When you drop a partition of a global index, Oracle Database marks the next index partition UNUSABLE. You cannot drop the highest partition of a global index.
split_index_partition: Use the split_index_partition clause to split a partition of a global range-partitioned index into two partitions, adding a new partition to the index. This clause is not valid for hash-partitioned global indexes. Instead, use the add_hash_index_partition clause.
coalesce_index_partition: This clause is valid only for hash-partitioned global indexes. Oracle Database reduces by one the number of index partitions. Oracle Database selects the partition to coalesce based on the requirements of the hash function. Use this clause if you want to distribute index entries of a selected partition into one of the remaining partitions and then remove the selected partition.
Define a Local Partitioned Index
In order to define a local partitioned index, you only have to add the keyword LOCAL to the end of the CREATE INDEX statement, as in
CREATE INDEX idxA ON tabA(colA) LOCAL ();
The local index will automatically be partitioned in the same way as the underlying table. You can specify which tablespace a local index's partitions will be stored in.
Prefixed and non-prefixed
A prefixed index includes the columns used to partition the underlying table at the beginning of the column list for the index, the left of the column list. Any local index that does not include these columns is a non-prefixed partitioned index. Both prefixed and non-prefixed local indexes are partitioned on the underlying column(s) used to partition the associated table. For this example, assume that your underlying table contains three columns:colA, colB, and colC. The table is partitioned on colA. A local prefixed index could contain colA and colB, and it would be partitioned on colA. A local non-prefixed index may only contain colB, but it would still be partitioned on colA.
Prefixed versus non-prefixed indexes
There are advantages to using each type of index. When you have a local index that is prefixed, the Oracle optimizer will automatically know to eliminate both index partitions and table partitions if the partitioning key is a part of the WHERE clause. This matching provides the fastest query execution. If an index is not prefixed, you can specify a different column and still benefit from the improved performance. For instance, you could have a table that is partitioned by month and a non-prefixed local index that is partitioned by account number. Assuming that a query asked for the sales for a particular month for a particular account, Oracle could use the index to quickly locate the account number but would not have to search table partitions that did not contain the correct month. The next lesson explains global partitioned indexes.
[1] equipartitioning: The partitioning of one base table partition for each nested table partition is called equipartitioning.
