Checking whether an Index is Used

You can check whether the index you created to support selection is used as follows:

  1. Open a second session and choose System ® Utilities ® SQL Trace.
  2. In the next screen choose Trace on.
  3. In the first window, perform the action in which the index is to be used.
  4. In the second session, choose Trace off and then List trace.
  5. The output depends on the database system. You can use the EXPLAIN function on the critical statements (PREPARE, OPEN, REOPEN) to determine the index used.

If the database system uses a cost-based optimizer, you should perform the action in step 3 with a representative data set, since a cost-based optimizer tries to find the best index based on statistics.