Re: When does a query use an Index?

The best way to find out if a particular query will use an index, and if so in what manner, is to run an explain plan for the query.

First you need a plan_table in an accessible schema; look at utlxplan.sql, a script supplied with Oracle, that creates this table. Then you execute an explain plan from SQL*plus, as with:

EXPLAIN PLAN SET statement_id='any_string' FOR SELECT * FROM ...; Replace 'any_string' with something meaningful to you.

Then query the explain plan:

      SELECT
        LPAD(' ', 2*(level-1)) || operation || ' ' || options || ' ' ||
        object_name || ' ' || DECODE(id, 0, 'Cost = ' || position)
query_plan
      FROM plan_table
        START WITH id = 0 and statement_id = 'any_string'
        CONNECT BY prior id = parent_id and statement_id = 'any_string';

You may get something like:
SELECT STATEMENT Cost =
  NESTED LOOPS
    TABLE ACCESS FULL CST_HISTORY
    INDEX UNIQUE SCAN I_SSN_EMPL You read this depth-first. Statements at the same nesting depth are performed sequentially. This tells you your SELECT statement does a full table scan and hits index I_SSN_EMPL for some related data, together in a nested loop. Typically, Oracle will hit an index and then get the related row by the rowid stored in the index. In the case shown here, the row isn't accessed because all the columns needed are in the index itself. The Cost has a value only when using cost-based optimization (vs. rule-based).

At first, making sense of the explain plan is hard, but you'll get better at it with practice. Read the Oracle documentation on explain plan.

The answers to your questions lie within the explain plan. - Dan Clamage dclamage_at_idcomm.com

> 1. Does a query use an Index when LIKE is used?
> 2. Does a query use an Index when an outer join is performed?
> 3. Does an Index get used when a view is used? The book says 'under
> certain conditions an Index will not be used' but doesn't say what they
> are.
Received on Tue Sep 23 1997 - 00:00:00 CDT