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