DB2 10 contains a whole bunch of great new stuff, but my personal favourite has got to be the new INCLUDE syntax for CREATE INDEX.
What it does is quite simply magical – it allows you to “add” columns to an index which are *not* used for uniqueness, but are in the index of course. This means you must so allow that wonderful little Y to appear in the INDEXONLY column in the PLAN_TABLE when you BIND / REBIND with EXPLAIN(YES) – You are all doing that, aren’t you?
Up until DB2 10, indexes “bred” because the business process *needed* a unique key for COL, COL2, and COL3, but for INDEXONLY Y access you *needed* COL5 and COL6; thus leading to a second basically pointless index coming into existence. Now of course, the Optimizer is a clever little devil and may, in its infinite wisdom, decide never to use the first index, but an INSERT causes the odd -811. The end user suffers under the extra CPU, disk space and I/O time of two indexes. Now with INCLUDE syntax, you no longer need that second index.
DB2 10 new INCLUDE syntax for CREATE INDEX
The following indexes existed for the following query runs (I did a full runstats of course!)
For QUERYs 1 – 34
CREATE TYPE 2 UNIQUE INDEX IQA061HU.IQAXY1092
ON IQA061HU.IQATY109
( STMT_KEY
, STMT_TYPE
)
USING STOGROUP SYSDEFLT
BUFFERPOOL BP0
FREEPAGE 0
PCTFREE 10
CLOSE YES;Leaf pages 2079 space 10800
For QUERYs 21 – 34
CREATE TYPE 2 UNIQUE INDEX IQA061HU.IQAXY1093
ON IQA061HU.IQATY109
( STMT_KEY
, STMT_TYPE
, ISOLATION_LEVEL
)
USING STOGROUP SYSDEFLT
BUFFERPOOL BP0
FREEPAGE 0
PCTFREE 10
CLOSE YES;Leaf pages 2294 space 10800
For QUERYs 41 – 54
CREATE TYPE 2 UNIQUE INDEX IQA061HU.IQAXY109I
ON IQA061HU.IQATY109
( STMT_KEY
, STMT_TYPE
)
INCLUDE (ISOLATION_LEVEL)
USING STOGROUP SYSDEFLT
BUFFERPOOL BP0
FREEPAGE 0
PCTFREE 10
CLOSE YES;Leaf pages 2294 space 10800
The queries looked like
EXPLAIN ALL SET QUERYNO = 1, 21, 41 FOR SELECT STMT_KEY, STMT_TYPE FROM IQA061HU.IQATY109 ; COMMIT ; EXPLAIN ALL SET QUERYNO = 2, 22, 42 FOR SELECT STMT_KEY, STMT_TYPE FROM IQA061HU.IQATY109 WHERE STMT_KEY = X'01329149008E899B000001D6' ; COMMIT ; EXPLAIN ALL SET QUERYNO = 3, 23, 43 FOR SELECT STMT_KEY, STMT_TYPE FROM IQA061HU.IQATY109 WHERE STMT_KEY = X'01329149008E899B000001D6' AND STMT_TYPE = 'M' ; EXPLAIN ALL SET QUERYNO = 11, 31, 51 FOR SELECT STMT_KEY, STMT_TYPE, ISOLATION_LEVEL FROM IQA061HU.IQATY109 ; COMMIT ; EXPLAIN ALL SET QUERYNO = 12, 32, 52 FOR SELECT STMT_KEY, STMT_TYPE, ISOLATION_LEVEL FROM IQA061HU.IQATY109 WHERE STMT_KEY = X'01329149008E899B000001D6' ; COMMIT ; EXPLAIN ALL SET QUERYNO = 13, 33, 53 FOR SELECT STMT_KEY, STMT_TYPE, ISOLATION_LEVEL FROM IQA061HU.IQATY109 WHERE STMT_KEY = X'01329149008E899B000001D6' AND STMT_TYPE = 'M' ; COMMIT ; EXPLAIN ALL SET QUERYNO = 14, 34, 54 FOR SELECT STMT_KEY, STMT_TYPE, ISOLATION_LEVEL FROM IQA061HU.IQATY109 WHERE STMT_KEY = X'01329149008E899B000001D6' AND STMT_TYPE = 'M' AND ISOLATION_LEVEL = 'CS' ;
Results were
QUERY QNO PNO SQ M TABLE_NAME A CS INDEX IO ---------+---------+---------+---------+---------+---------+-- 00001 01 01 00 0 IQATY109 I 00 IQAXY1092 Y 00002 01 01 00 0 IQATY109 I 01 IQAXY1092 Y 00003 01 01 00 0 IQATY109 I 02 IQAXY1092 Y 00011 01 01 00 0 IQATY109 R 00 N 00012 01 01 00 0 IQATY109 I 01 IQAXY1092 N 00013 01 01 00 0 IQATY109 I 02 IQAXY1092 N 00014 01 01 00 0 IQATY109 I 02 IQAXY1092 N 00021 01 01 00 0 IQATY109 I 00 IQAXY1092 Y 00022 01 01 00 0 IQATY109 I 01 IQAXY1092 Y 00023 01 01 00 0 IQATY109 I 02 IQAXY1092 Y 00031 01 01 00 0 IQATY109 I 00 IQAXY1093 Y 00032 01 01 00 0 IQATY109 I 01 IQAXY1093 Y 00033 01 01 00 0 IQATY109 I 02 IQAXY1093 Y 00034 01 01 00 0 IQATY109 I 03 IQAXY1093 Y 00041 01 01 00 0 IQATY109 I 00 IQAXY109I Y 00042 01 01 00 0 IQATY109 I 01 IQAXY109I Y 00043 01 01 00 0 IQATY109 I 02 IQAXY109I Y 00051 01 01 00 0 IQATY109 I 00 IQAXY109I Y 00052 01 01 00 0 IQATY109 I 01 IQAXY109I Y 00053 01 01 00 0 IQATY109 I 02 IQAXY109I Y 00054 01 01 00 0 IQATY109 I 02 IQAXY109I Y
As can be seen, the first block gave especially bad results when the ISOLATION_LEVEL was added – leading to a tablespace scan in the worst case scenario!
Creating the second index alleviated the problem, and as you can see, the access’s all went INDEXONLY = Y, but now we have two indexes! That is double the disk space and double the time for updates, inserts and deletes. After I dropped the first two indexes and then created the INCLUDE one, you can see that the access is the same (Apart from the 2 columns for the query 54 of course – Here the original index would actually be better because the column is in the predicate list not just in the select list!) and now there is only the one index „to worry about“ – for RUNSTATS, REORGs, etc.
Now all you need to do is find where you have these „double“ defined indexes. The method is to look for any unique indexes where there exists another index with fewer equal columns. Easier said than done… however LISTSERV can help you here! If you are registered you can easily find some SQL written by Larry Kirkpatrick that very handily does nearly what you need! Search for “This could be a useful query when going to V10” to get a really neat SQL that you can simply SPUFI (You must do one tiny change and that is the SPACE line must be rewritten to look like INTEGER(A.SPACEF) AS UNIQ_IX_KB, to actually get the allocated space) to get this result from my test database:
---------+---------+----- --+---------+---------+---------+ UNIQUE_IX_TO_DEL UNIQ_IX_KB IX_WITH_PART_UNIQUE ---------+---------+--------+---------+---------+---------+ IQA061HU.IQAXY1092 10800 IQA061HU.IQAXY1093 IQA061HU.IQAXY1092 10800 IQA061HU.IQAXY109I
Here you can see that it is correctly telling me to drop the 1092 index as it is completely redundant and of course it finds it again due to my INCLUDEd column index with the 109I Now of course what you actually want to do is the inverse of this query. You probably want to DROP the longer index *and* the shorter index and then recreate the shorter with INCLUDE columns like the longer. Now is also a good time to think about using DSN_VIRTUAL_INDEXES to check the effects of the DROP before you actually do it… also take a look in my Newsletter from June 2011 for details about finding dead indexes (or ask me to resend it).
Feel free to send me your comments and ask questions.
TTFN,
Roy Boxwell
Senior Architect