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.
Each index partition mirrors a table partition.
You set up the table partitions when you define or maintain the table.
Each index partition mirrors a table partition
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.
Figure 2-6: Locally partitioned index architecture
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.
