I was recently demonstrating our SQL Optimizer for DB2 LUW solution when a question came up regarding comparing Access Plans in the Index Expert portion of the Optimizer. Excellent question indeed! However, before I go any further, perhaps a little background on the Index Expert is warranted.
The Index Expert allows you to simulate indexes for a SQL statement. We use our technology to generate virtual indexes (i.e. they’re not created on the database) in order to fetch unique virtual access plans. We can also ask DB2 to suggest virtual indexes as part of the investigation. Heck, you can even create your own indexes if you wish. The idea is to then examine the virtual access plans to see if you would gain an improvement in performance if you apply the indexes. Even better, you can perform a Batch Run, which physically applies the indexes to the database, fetches the access plan and several metrics, and then drops the indexes when the process completes. That way, you have more information to help guide your decision on the proposed indexes.
So, the question was whether you can compare the access plans of the indexes that were suggested. Whoops. The current version doesn’t allow you to do this. Fortunately, it appears that the next version (currently in Beta) eliminates this limitation. Hooray!
What I also find nice is that we’ve merged the SQL Optimizer and the Index Expert windows. As a result, I can examine suggested rewrites of my SQL statement AND suggested indexes all in one interface. No more bouncing between windows to get the complete picture. In the below screenshot, you’ll see that I’ve highlighted the new toolbar icons that make this possible.
Please keep in mind that I’m working with Beta software and Betas are subject to change.