Prior to the Jump Scan feature of DB2 10 when an index scan was executed on a composite index, DB2 scanned a large range of the index between start-stop keys or the entire index if no start-stop keys were present while applying predicates to locate qualifying keys. The start-stop keys define how much of an index should be examined.
Queries against tables with composite (multi-column) indexes present a particular challenge when designing indexes for tables.
When an index query contains predicates that are inconsistent with a composite index, or contains predicates that can only be partially applied as start-stop keys, this is known as an Index Gap. A query can have a constrained index gap or an unconstrained index gap.
Index gaps are a property of a particular query being executed and not a general property of an index.
As DBA's, we typically defined additional indexes to workaround the challenges.
Let's take a look at the example and how it worked prior to DB2 10
The following query was executed. The table has an index
ix1 on three columns:
PRODUCT_ID, ORDER_LOCATION, and ORDER_AMOUNT.In this query there is an Index gap, on
ORDER_LOCATION column because there areno predicates to restrict that column. DB2 would have to scan a large range.
In DB2 10, with Jump Scan all the keys that are marked with an x were skipped by the index manager.
Want to learn more about Jump Scan ? or see it in action for yourself?
Download the vmware appliance (trial version) of DB2 10 which includes the DB2 Technology Explorer and you can go through this workshop and other's to learn about adaptive compression, custom analytics, multi-temperature storage (MTS) and more....
The Technology Explorer includes scripts and tutorials to take you through each of the features and enable you to learn using a live DB2 system.
You can download the vmware appliance at
http://www-01.ibm.com/software/data/db2/linux-unix-windows/download.html
