2012-03: INCLUDE Index Usage with Examples to find Redundant Indexes

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