Databases Reference
In-Depth Information
Understanding Partitioned Indexes
Partitioned indexes offer many advantages over their non-partitioned counterparts. The key advantages
of using partitioned indexes include:
•
Performance benefits
•
Loading data via DML operations.
•
Loading data via DDL operations.
•
Querying data via SELECT statements.
•
Maintenance benefits
•
Rebuilding indexes.
Setting indexes unusable or invisible at a partition-level.
You can create partitioned indexes either as locally partitioned or globally partitioned. Locally
partitioned indexes can only exist on partitioned tables, while globally partitioned indexes can be
created on partitioned or non-partitioned tables. You can also create non-partitioned indexes on
partitioned tables. The most common configuration for partitioned indexes is to create locally
partitioned indexes on a partitioned table, simply because the overall benefits and trade-offs of this
configuration generally beat out both globally partitioned and non-partitioned indexes on partitioned
tables.
•
■
Note
Creating non-partitioned indexes on partitioned tables is identical to creating non-partitioned indexes on
non- partitioned tables. Refer to the “Maintaining Indexes on Partitioned Tables” section for more information on
the use of non-partitioned indexes on partitioned tables.
Creating a Locally Partitioned Index
The most common type of partitioned index is the locally partitioned index. Locally partitioned indexes
can only be created on partitioned tables. As specified by the name, “local” means there is a direct
relationship between entries for an index and the corresponding data. There is a one-to-one relationship
between data partitions and index partitions. If you have a table partitioned by range based on dates and
you have a partition for every month of the year, then for all the data for the January 2012 partition, you
have, for each index created, the corresponding index entries in the January 2012 index partition(s). See
Figure 6-1 for an example of the architecture between data and index partitions for a locally partitioned
index.
