Databases Reference
In-Depth Information
7.7
Indexing Range Partitioned Data
Commercial databases have two strategies for indexing range partitioned data, namely
global indexes and local indexes. Global indexes index over all ranges in the table.
They provide the same service and functionality that an index would serve on a non-
partitioned table, holding keys and RIDs for all records in the table. Local indexes are
quite similar but index only the records stored in a single range partition. With local
indexes every range partition in the table has its own physical instance of the indexes
defined on the table. In a local index, all keys within the index refer exclusively to the
records of data stored in the table for that range partition. The index itself is parti-
tioned on the same columns as the partition columns of the ranged partitioned table
that is being indexed.
Local indexes are often more efficient because if they are stored as B+trees they may
reduce the height of the tree, improving search time, and they can be operated on inde-
pendently, providing huge efficiencies for roll-in and roll-out and utility operations, which
will be discussed later.
When using a global index, the index may need to be fully rebuilt when some table
maintenance operations are performed, even when those operations only impact specific
(or one) range partition. Removal of keys from the global index by range is clearly far
less efficient as well, as discussed below. However, global indexes are the natural way to
enforce uniqueness for secondary indexes that are not defined along the range partition
columns. Not all commercial database products currently support both index types.
3
Figure 7.7
Matching range partitioning with index clustering.
3
DB2 for Linux Unix and Windows only supports global indexes over range partitioned tables as
of v9.1. If you are using a more recent version, please check the latest product documentation.
