Re: How to check if a query is using an index ?

From: Austin <hacketta_57_at_yahoo.com>
Date: 22 Aug 2003 04:17:20 -0700
Message-ID: <c59a59b7.0308220317.5560787e@posting.google.com>

"F.Marchioni" <fmarchioniNIENTESPAM_at_libero.it> wrote in message news:<D0j1b.257242$lK4.7949932_at_twister1.libero.it>...
> Dear all,
> I'd like to know if there's a way to check if a query is -actually- using an
> index.
> I've read that Oracle is able to evaluate what's better between a full table
> scan and using an index (true?)....so I'd like to know when the first
> solution
> is chosen...
> Thanks a lot in advance
> Francesco

Set trace on in SQL Plus e.g.

SQL> set autotrace traceonly explain
SQL> select * from ahacket2.test where upmj = 1 and upmt = 2;

Execution Plan

---

          0
SELECT STATEMENT Optimizer=CHOOSE

....................................................................................................................................
          1                  0

  TABLE ACCESS (BY INDEX ROWID) OF 'TEST'

....................................................................................................................................
          2                  1

    INDEX (UNIQUE SCAN) OF 'TEST_PK' (UNIQUE)

....................................................................................................................................

SQL> set autotrace off

Here we can see that the query uses a unique scan of the TEST_PK index.

Here's a full table scan:

SQL> select * from ahacket2.test;

Execution Plan

---

          0
SELECT STATEMENT Optimizer=CHOOSE

....................................................................................................................................
          1                  0

  TABLE ACCESS (FULL) OF 'TEST'

....................................................................................................................................

SQL> set autotrace off

There is a lot more to Oracle excution plans than index scan/full table scan but this shows you how to find out what oracle is going to do. Received on Fri Aug 22 2003 - 06:17:20 CDT