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.
Bottlenecks of having Index Gaps:
1. In case of index gaps index needs to process many unnecessary keys.
2. Predicates needs to be applied against each key in the index between the start and stop keys.
3. As a result, index scan slows down.
Solution to index gap prior to DB2 v10.1:
Create new index on the predicate columns that will make the index read efficient.
I2: create INDEX EMPLOYEE_ID2 on EMPLOYEE
( FIRSTNME ASC, LASTNAME ASC) ALLOW REVERSE SCAN;
I created an index I2 for the Q2 where we are having the index scan. Now Q2 will executed with the optimal plan.
But if we start creating a new composite index for every OLTP application then there will be too many indexes in our databases. For every new indexes there will be additional storage requirement, additional administration, additional maintenance etc. It might happen that CPU cost of maintaining the indexes can outweigh whatever performance gains a new index might deliver. Ohh God 😮 I need better solution for this.
Index Jump Scan:
Here comes the gem. One solution for all problems in the prior versions. Index Jump scan makes a transparent and better use of the composite indexes created for tables. DB2 optimizer will automatically build a execution plan which includes a jump scan whenever there is a index gap.
What jump scan does that it identifies qualifying keys for small sections of a composite index where there are gaps, and fills these gaps with the qualifying keys. The result is that the index manager skips over parts of the index that will not yield any results.
How it works:
On the left there is a row wise scan for the query Q2 which will also include the unnecessary data that will not yield any result. And on the right, Index Jump scan occurred. Optimizer identified only the qualifying rows and will skip the unnecessary data.
Advantages of Jump Scan:
1. Reduce storage
2. Reduce I/O’s
3. Reduce performance impacts on writes
4. Control index usage in access plans and in monitoring and potentially remove unused indexes
Troubleshooting and Monitoring Tips:
Some useful tips for the index Jump scan monitoring and troubleshooting
We can monitor jump scans using this monitor element. We can check if the jump scan is occurring at the expected rate or not using this monitoring method.
2. MON_GET_INDEX Table Function
We can use this table function to check the number of Jump Scans occurred while using the given indexes.
Here is the sample query:
SELECT S.INDSCHEMA, S.INDNAME, T.INDEX_SCANS, T.INDEX_JUMP_SCANS
FROM TABLE(MON_GET_INDEX(‘TESTCHEMA’,‘EMPLOYEE’, -1)) as T,SYSCAT.INDEXES AS S
WHERE T.TABSCHEMA = S.TABSCHEMA
AND T.TABNAME = S.TABNAME
AND T.IID = S.IID
ORDER BY INDEX_JUMP_SCANS;
INDSCHEMA INDNAME INDEX_SCANS INDEX_JUMP_SCANS
——————- ——————— ———————- —————————
TESTCHEMA EMPLOYEE_IND1 146 30
As we can see in the output we can clearly see the number of normal index scans and the index jump scans.
This is the most handy for monitoring and tracking the Jump scans occurring in the table.
3. If Jump scans are not occurring at the expected place then we can check in the DB2 explain plan the cause of the index gap and why index scan is not preferred over the normal scan. DB2 Explain facility to troubleshoot the cause. We need to understand that DB2 cost based optimizer will always prepare a plan with optimal cost and it is not necessary that it will include the jump scan always. Sometime it might happen that jump scan is not resulting into the optimal execution plan.
4. Index scan will not be applicable for following indexes
a) Range-clustered table indexes
b) Extended indexes
c) XML indexes
d) Text Indexes (for Text Search)
Presentation goes well in time and judges also gave the positive feedback for the presentation contents. I would like to attach the references that one must visit while studying this topic.
1) Episode at DB2NightShow by Scott Hayes
The DB2Night Show #112: DB2 LUW 10 Index Jump Scans & Index Best Practices
2) IBM Info Center:
3) Blog by Steve Rees:
4) Blog by Jan Koblenzer:
Please find my presentation at the following link on slide share:
I will suggest to check out complete episode to get some of the awesome performance tips shared by other DB2 champs.