RelationalDBDesign RelationalDBDesign

Partitioned Tables   «Prev  Next»
Lesson 6 Local indexes
ObjectiveCreate a local index on a partitioned table.

Local Indexes Partitioned Oracle Table

When you create a local index, it is automatically equipartitioned 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 MouseOver illustrates the relationship between a local index and its corresponding table.

  1. Each index partition mirrors a table partition.
  2. You set up the table partitions when you define or maintain the table.
Each index partition mirrors a table partition

Local Indexes
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.