DB2 Version 10.1 for Linux, UNIX, and Windows

Improved performance for queries on tables with composite indexes

The DB2® query optimizer can now create additional access plans that might be more efficient for queries with index gaps in their start-stop keys by using a jump scan operation. For example, index gaps are common in queries with multiple predicates that are issued against tables with composite indexes. Jump scans eliminate the need for index gap avoidance tactics such as the creation of additional indexes.

Problem: Index gaps

For workloads involving many ad-hoc queries, it is often difficult to optimize a database for high performance. Queries against tables with composite (multi-column) indexes present a particular challenge. Ideally, a query's predicates are consistent with a table's composite index. This would mean that each predicate could be used as a start-stop key, which would, in turn, reduce the scope of the index needing to be searched. When a query contains predicates that are inconsistent with a composite index, this is known as an index gap. As such, index gaps are a characteristic of a query, not of a table's indexes.

For example, consider a table T with integer columns A, B, and C, and a composite index defined on the columns A, B, and C. Now, consider the following query against table T:

SELECT * FROM t WHERE a=5 AND c=10

This query contains an index gap on column B in the composite index (this is assuming that the access plan contains an index scan on the composite index).

In the case of an index gap, the index scan will likely have to process many unnecessary keys. The predicates on the non-leading columns of the index would likely need to be applied individually against each key in the index that satisfies the start-stop keys. This slows down the index scan as more rows need to be processed, and additional predicates need to be evaluated for each key. Also, DB2 must sequentially examine all keys in what could be a large range.

To avoid index gaps you can define additional indexes to cover the permutations of query predicates likely to appear in your workloads. This is not an ideal solution, because defining additional indexes requires additional database administration and consumes storage capacity. Also, for workloads with many ad-hoc queries, it can be difficult to anticipate which indexes would be needed.

Solution: jump scan enablement

In DB2 V10.1, the query optimizer can build an access plan that uses the jump scan operation when queries contain index gaps. In a jump scan operation, the index manager identifies qualifying keys for small sections of a composite index where there are gaps, and fills these gaps with these qualifying keys. The result is that the index manager skips over parts of the index that will not yield any results.

Note: When evaluating queries, there can be cases where the query optimizer builds an access plan that does not include a jump scan operation, even if index gaps are present. This would occur if the query optimizer deems an alternative to using a jump scan to be more efficient.