This post is based on one of the ‘Index Jump Scan‘ – one of the terrific feature introduced in DB2 10.1. I presented the topic at DB2NightShow Finals-2. The theme for this episode was ‘DB2 Performance’ and I found this topic at the right time.
This was actually a courageous decision of presenting this topic in front of the Scott – one of the pioneer of this topic.
Still I tried my best to introduce the community with this topic and present a comparative study of the scenario without and with the Index Jump Scan. Here is the quick summary of my presentation.
Being a DBA I always worked on the long ad-hoc queries which has been written for the reporting tools like actuate reports and Birt reports. It is always painful to tune these queries because of the number of predicate conditions and the composite indexes already designed on the tables involved in such queries. Frankly speaking my face used to be turned down when developers asked me to tune such a long queries for which I need to scroll my editor. 😉
Ideally query predicates should be consistent with that of the composite index on the column. For e.g
Q1: select FIRSTNME,MIDINIT,LASTNAME
from EMPLOYEE where FIRSTNME=’CHRISTINE’
and MIDINIT=‘I’ and LASTNAME=‘HAAS’;
I1: create index EMPLOYEE_ID1 on EMPLOYEE
( FIRSTNME ASC,MIDINIT ASC, LASTNAME ASC) ALLOW REVERSE SCAN;
I have query Q1 in which WHERE condition is specified on the columns FIRSTNME, MIDINIT and LASTNAME. For optimal performance I created a composite index I1 on combination of these columns. Such queries where the query predicates are consistent with that of the columns in the composite indexes such queries are said to be consistent. Such queries will provide optimal performance.
Unfortunately, this is not the case in most of the application scenarios. Queries are in-consistent with that of the composite indexes. Following is the sample example of the in-consistent queries.
Q2: select FIRSTNME, MIDINIT, LASTNAME
from EMPLOYEE where FIRSTNME=’CHRISTINE‘
Predicates of the query Q2 are not consistent with index I1 since column MIDINIT is not there in the WHERE clause.
In such case the query is said to be in-consistent and it is said to have Index Gap. In our example, we are having the index gap for query Q2 on MIDINT column.
Read the rest of this entry »