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:
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.