How to Check Whether an Index Is Being Used 

Use

Sometimes a suitable index is not used for a selection, even if one exists. Which index is used depends on which optimizer the database system uses. The following tells you how to find out which index the database system uses for a selection.

Procedure

To check whether an index is being used, proceed as follows:

  1. Open a second session and choose System ® Utilities ® Performance Trace there.
  2. Select SQL Trace and choose Trace on.
  3. In the first window, carry out the action for which you want the system to use the desired index.
  4. Choose Trace off and then Trace list.
  5. The display generated depends on the database system used. You can find the index used with the function EXPLAIN on the critical statements (PREPARE, OPEN, REOPEN).

If your database system uses a cost-based optimizer, you should carry out step 3 with as representative a data volume as possible, since a cost-based optimizer finds the best index on the basis of statistics.