Db2 Tools for z/OS



Testing Virtual Indexes with IBM SQL Performance Analyzer by Tom Hubbard

By CALENE JANACEK posted Thu July 09, 2020 12:59 PM


Did you know that SQL Performance Analyzer gives you the ability to use virtual index configurations to calculate the impact that those change might have on the cost of your SQL?  SQLPA supports the ability to both virtually drop real indexes and/or create new virtual indexes. This gives you the ability to test the impact on SQL cost of proposed index changes.

For example, to calculate the cost of SQL queries, you can drop a real index virtually, then create and drop many virtual indexes to simulate a different index configuration. The virtual drop of a real index causes the EXPLAIN command to exclude a virtually dropped real index from the cost calculation of SQL queries, but it does not remove the physical index from the Db2 catalog. Creating a virtual index causes the EXPLAIN command to include a virtual index for the cost calculation, but it does not require processor resources to build a virtual index even when the table size of the index target is large. These virtual index features help to design more efficient indexes without having to create and/or dropping the indexes.

Check out the implementation in this library article under SQLPA. Click here for direct access.