How to Check Whether an Index Is Being Used
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.
To check whether an index is being used, proceed as follows:
Open a second session and choosethere.
SQL Traceand choose
In the first window, carry out the action for which you want the system to use the desired index.
Trace offand then
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.