10.3 Partitioning Indexes

So what, you may wonder, happens to the indexes on partitioned tables? The answer is that you have to choose whether each index will stay intact (referred to as a global index), or be split into pieces corresponding to the table partitions (referred to as a local index). Furthermore, with a global index, you can choose to partition the index in a different manner than the table was partitioned. When you throw the fact that you can partition both b-tree and bit-map indexes into the mix, things can become overwhelming. When you issue a SELECT, UPDATE, or DELETE statement against a partitioned table, the optimizer can take several routes to locate the target rows:

• Use a global index, if one is available and its columns are referenced in the SQL statement, to find the target rows across one or more partitions.

• Search a local index on every partition to identify whether any particular partition contains target rows.

• Define a subset of the partitions that might contain target rows, and then access local indexes on those partitions.

Although global indexes might seem to be the simplest solution, they can be problematic. Because global indexes span all of the partitions of a table, they are adversely affected by partition maintenance operations. For example, if a partition is split into multiple pieces, or if two partitions are merged into one, all global indexes on the partitioned table are marked as UNUSABLE and must be rebuilt before they can be used again. When modifying a partitioning scheme, you have your choice of rebuilding the global indexes manually, or of using the UPDATE GLOBAL INDEXES clause.