Problem solve Get help with specific problems with your technologies, process and projects.

Determining how indexes are being used

If I have five indexes on a table, what is the best way to determine if all, some or none of the indexes are being used?
The best approach would be to make sure you have run EXPLAIN on all of your production plans and packages. Then examine the PLAN_TABLE output for those indexes. For example:

WHERE  ACCESSNAME IN (put your list of indexes here);
Of course, this will not show the dynamic SQL that uses any of these indexes. There are tools on the market that can help you to gather SQL usage statistics and access path information for both static and dynamic SQL statements.

Consider acquiring one of these tools if you use a lot of dynamic SQL. One such offering is BMC Software's SQL Performance for DB2 solution. Click here for more information.

Editor's note: Craig Mullins is director of technology planning at BMC Software Inc.

Editor's note: Do you agree with this expert's response? If you have more to share, post it in one of our .VO7aaqqaAFk.0@/search390>discussion forums.

This was last published in November 2004

Dig Deeper on IBM DB2 management

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.