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

"F.Marchioni" <fmarchioniNIENTESPAM_at_libero.it> wrote

> I'd like to know if there's a way to check if a query is -actually- using an
> index.

Look up the SQL statement called EXPLAIN PLAN in your Oracle SQL Reference Manual.

It creates an execution plan for the query - the results of which is stored in a table called PLAN_TABLE.

If you do not have a plan table, ask your DBA to create one for you, or manually find the script called utlxplan.sql in ORACLE_HOME/rdbms/admin and run that.

You can also use AUTOTRACE in SQL*Plus for this.

> I've read that Oracle is able to evaluate what's better between a full table
> scan and using an index (true?)....

Simplistically, yes. The CBO, Oracle's Cost Based Optimizer, is a lot more intelligent, given a descent chance.

--
Billy