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

From: Tim Kearsley <tim.kearsley_at_milton-keynes.gov.uk>
Date: 22 Aug 2003 05:35:06 -0700
Message-ID: <725736ef.0308220435.5b4cd757@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

Hello Francesco,

I think the simplest way is to get Oracle to show you its execution plan for the query by using the EXPLAIN PLAN procedure. For example, if your query is:

SELECT emp_no, emp_name FROM emp WHERE emp_name LIKE 'S%';

then you simply issue the command:

EXPLAIN PLAN FOR SELECT emp_no, emp_name FROM emp WHERE emp_name LIKE 'S%';

The execution plan is inserted into, by default, a table called PLAN_TABLE which must exist for the EXPLAIN to work. You can name a table other than PLAN_TABLE by saying EXPLAIN PLAN INTO <table> FOR <query>.

If PLAN_TABLE doesn't already exist you can create it by running the script $ORACLE_HOME/rdbms/admin/utlxplan.sql.

Regards,

Tim Kearsley
Database Manager
Milton Keynes Council Received on Fri Aug 22 2003 - 07:35:06 CDT