Partitioned indexes have the same advantages as partitioned tables. Accessing smaller pieces instead of one index on the entire table increases performance when properly executed. There are local and global indexes, and prefixed or nonprefixed indexes.
- A local index has been partitioned; each piece is a local index.
- A global index is just a regular nonpartitioned index.
- A prefixed index is when the leftmost part of the index is the partition key, whereas a nonprefixed index can be costly to access because the partition key is not indexed.
If a partition of a table with a global index is dropped, then the corresponding global index is invalidated. If a partition of a table with a local prefixed index is dropped, then the local index is also dropped.
The initialization parameter SKIP_UNUSABLE_INDEXES allows the user to disable error reporting of indexes and index partitions marked unusable. If you do not want the database to choose a new execution plan to avoid using unusable segments, you should set this parameter to FALSE (the default value is TRUE).
The following is an example of a local prefixed partitioned index (the most common type). The index name is DEPT_INDEX, and the index is on the DEPTNO column of the DEPT table. The index is split into three pieces (D1, D2, and D3) that are located in three tablespaces (DEPT1, DEPT2, and DEPT3) that are striped differently from the location of the corresponding table data. This ensures that accessing information from a table partition and its corresponding index partition will result in accessing two physical disk drives instead of one—given that DEPT1–DEPT3 are tablespaces that correspond to datafiles on different physical disks.
You can get the information regarding partitioned indexes by accessing DBA_INDEXES:
TIP
Indexes that are partitioned (local indexes) should also be prefixed, meaning the partitioning key is the leading edge of the index.
Leave a Reply