A partitioned index in Oracle 11g is simply an index broken into multiple pieces. By breaking an index into multiple physical pieces, you are accessing much smaller pieces (faster), and you may separate the pieces onto different disk drives (reducing I/O contention). Both b-tree and bitmap indexes can be partitioned. Hash indexes cannot be partitioned. Partitioning can work several different ways. The tables can be partitioned and the indexes are not partitioned; the table is not partitioned but the index is; or both the table and index are partitioned. Either way, the cost-based optimizer must be used. Partitioning adds many possibilities to help improve performance and increase maintainability.
There are two types of partitioned indexes: local and global. Each type has two subsets, prefixed and non-prefixed. A table can have any number or combination of the different types of indexes built on its columns. If bitmap indexes are used, they must be local indexes. The main reason to partition the indexes is to reduce the size of the index that needs to be read and to enable placing the partitions in separate tablespaces to improve reliability and availability.
Oracle also supports parallel query and parallel DML when using partitioned tables and indexes, adding the extra benefit of multiple processes helping to process the statement faster.
Local (Commonly Used Indexes)
Local indexes are indexes that are partitioned using the same partition key and same range boundaries as the partitioned table. Each partition of a local index will only contain keys and ROWIDs from its corresponding table partition. Local indexes can be b-tree or bitmap indexes. If they are b-tree indexes, they can be unique or nonunique.
Local indexes support partition independence, meaning that individual partitions can be added, truncated, dropped, split, taken offline, etc., without dropping or rebuilding the indexes. Oracle maintains the local indexes automatically. Local index partitions can also be rebuilt individually while the rest of the partition is unaffected.
-
Prefixed Prefixed indexes are indexes that contain keys from the partitioning key as the leading edge of the index. For example, let’s take the PARTICIPANT table again. Say the table was created and range-partitioned using the SURVEY_ID and SURVEY_DATE columns and a local prefixed index is created on the SURVEY_ID column. The partitions of the index are equipartitioned, meaning the partitions of the index are created with the same range boundaries as those of the table (see Figure 1).
Figure 1. Partitioned, prefixed indexes
TIP
Local prefixed indexes allow Oracle to prune unneeded partitions quickly. The partitions that do not contain any of the values appearing in the WHERE clause will not need to be accessed, thus improving the statement’s performance.
-
Non-prefixed Non-prefixed indexes are indexes that do not have the leading column of the partitioning key as the leading column of the index. Using the same PARTICIPANT table with the same partitioning key (SURVEY_ID and SURVEY_DATE), an index on the SURVEY_DATE column would be a local non-prefixed index. A local non-prefixed index can be created on any column in the table, but each partition of the index only contains the keys for the corresponding partition of the table (see Figure 2).
Figure 2. Partitioned, non-prefixed indexes
For a non-prefixed index to be unique, it must contain a subset of the partitioning key. In this example, you would need a combination of columns, including the SURVEY_DATE and/or the SURVEY_ID columns (as long as the SURVEY_ID column was not the leading edge of the index, in which case it would be a prefixed index).
TIP
For a non-prefixed index to be unique, it must contain a subset of the partitioning key.
Global
Global partitioned indexes contain keys from multiple table partitions in a single index partition. The partitioning key of a global partitioned index is different or specifies a different range of values from the partitioned table. The creator of the global partitioned index is responsible for defining the ranges and values for the partitioning key. Global indexes can only be b-tree indexes. Global partitioned indexes are not maintained by Oracle by default. If a partition is truncated, added, split, dropped, etc., the global partitioned indexes need to be rebuilt unless you specify the UPDATE GLOBAL INDEXES clause of the ALTER TABLE command when modifying the table.
-
Prefixed Normally, global prefixed indexes are not equipartitioned with the underlying table. Nothing prevents the index from being equipartitioned, but Oracle does not take advantage of the equipartitioning when generating query plans or executing partition maintenance operations. If the index is going to be equipartitioned, it should be created as a local index to allow Oracle to maintain the index and use it to help prune partitions that will not be needed (see Figure 3). As shown in the figure, the three index partitions each contain index entries that point to rows in multiple table partitions.
Figure 3. Partitioned, global prefixed index
TIP
If a global index is going to be equipartitioned, it should be created as a local index to allow Oracle to maintain the index and use it to help prune partitions, or exclude those partitions that are not needed by the query.
-
Non-prefixed Global non-prefixed indexes should not be used as Oracle does not support them. They do not provide any benefits over normal B-tree indexes on the same columns, so they have no value.
Leave a Reply