While DPSIs provide gains in furthering partition independence, some queries may not perform as well. If the query has predicates that reference columns in a single partition and are therefore restricted to a single partition of the DPSI, the query benefits from this new organization. To accomplish this task, design the queries to allow for partition pruning through the predicates. This practice means that the leading column of the partitioning key has to be supplied in the query for DB2 to prune partitions from the query access path. However, if the predicate references only columns in the DPSI, it may not perform well because it may need to probe several partitions of the index. Other limitations of DPSIs include that they cannot be unique (some exceptions in DB2 9), and they may not be the best candidates for ORDER BYs.
|
Copyright © 2014 CA Technologies.
All rights reserved.
|
|