Blog
DB2utor

Categories

December 01, 2009

Virtual Index Cost Analysis

During the process of tuning SQL statements, DBAs will use the EXPLAIN statement to determine the access path taken by DB2 to retrieve the data.

In "Explaining How DB2 Will Get Data for a Given SQL Statement," I noted that DB2 has introduced a few other tables used by EXPLAIN. One of these tables is DSN_STATEMNT_TABLE, which contains an estimated cost in service units and milliseconds to execute the explained statement. During SQL development, this helps DBAs understand the cost ramifications of choosing one access path over another.

For instance, the DBA may review an access path and determine that the cost of executing the statement is too high. In response, the DBA may decide to experiment with different ways of coding the SQL statement to improve the access path. However, the final solution may require that a new index on the table be accessed. Prior to DB2 9 the only way to verify whether creating a new index would help in such situations was to actually go through the iterative process of creating the new index and then running runstats before performing an EXPLAIN on the statement. But with DB2 9, IBM introduced the DSN_VIRTUAL_INDEXES table. (Note: This was later applied to DB2 8 through APAR PK46687 with the name DSN_VIRTUAL_INDEX).

EXPLAIN will consider any index that's defined in the DSN_VIRTUAL_INDEX table, along with any real index defined in the catalog. DSN_VIRTUAL_INDEX is meant only for use through the Optimization Expert, the Optimization Service Center (OSC) monitor that's available as an add-on. The OSC install job creates this table with a default owner of DB2OSC. When I first installed the OSC, I tried to populate and use the default installed table, DB2OSC.DSN_VIRTUAL_INDEX, but I couldn't get EXPLAIN to use this table for index design. I figured I needed the OSC, so I gave up. Later though I learned that what I actually needed was a copy of the table created with my user ID. Just as the PLAN_TABLE needs to be under your user ID, all other EXPLAIN tables, including DSN_VIRTUAL_INDEXES, follow this format.

With DSN_VIRTUAL_INDEXES, not only can you simulate the creation of an index, you can simulate a dropped index. So you can gauge the impact of dropping an index without actually deleting.

This SQL statement currently uses index SYSIBM.DSNDTX03:

     SELECT *                           
     FROM SYSIBM.SYSTABLES              
     WHERE TBCREATOR = 'XXXXXXXX'        
       AND   NAME = 'YYYYYYYYYYYYYYYYYYYYYYYYYYYY’

The sample INSERT below simulates the DROP of the existing index (SYSIBM.DSNDTX03) on table SYSIBM.SYSTABLES, which contains columns TBCREATOR and TBNAME. This insert uses existing values in the catalog as well as an override for ENABLE and MODE along with COLNOx ordering.

Remember that the column order is very important. In this example, COLNO1 is 30 and COLNO2 is 32, representing the columns TBCREATOR and TBNAME.

INSERT INTO COLTR05.DSN_VIRTUAL_INDEXES                      
 (TBCREATOR, TBNAME, IXCREATOR, IXNAME, ENABLE, MODE,        
 UNIQUERULE, COLCOUNT, CLUSTERING, NLEAF, NLEVELS, INDEXTYPE,
 PGSIZE, FIRSTKEYCARDF, FULLKEYCARDF, CLUSTERRATIOF, PADDED  
, COLNO1, ORDERING1                                          
, COLNO2, ORDERING2)                                         
SELECT TBCREATOR, TBNAME, CREATOR, NAME, 'Y', 'D',           
 UNIQUERULE, COLCOUNT, CLUSTERING, NLEAF, NLEVELS, '2', 4,   
 FIRSTKEYCARDF, FULLKEYCARDF, CLUSTERRATIOF, PADDED          
 , 30, 'A'                                                   
 , 32, 'A'                                                   
 FROM SYSIBM.SYSINDEXES                                      
 WHERE CREATOR = 'SYSIBM'                                    
   AND NAME = 'DSNDTX03'                                     
   AND TBCREATOR = 'SYSIBM'                                  
   AND TBNAME = 'SYSTABLES'                                  

For more information about virtual indexes, see the "DB2 Performance Monitoring and Tuning Guide" and the "DB2 Application Programming and SQL Guide."