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 Start of the navigation path System Next navigation step Utilities Next navigation step Performance Trace End of the navigation path 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.