Re: SQL*PLUS and INDEX

Try this to find out if you use an index or not:

1- check if you have a plan_table table in your schema
2- if not, create it with oracle_home\rdbmsxx\admin\utlxplan.sql
3- execute the following:

explain plan set statement_id = 'X' for
<sql statement>;

4- Run this in SQL*Plus to get the execution plan:

SET heading on
SET pagesize 70

SELECT lpad(' ',2*level)||Operation||' '||Options|| ' '||object_name Q_Plan
FROM plan_table
WHERE statement_id = 'X'
connect BY prior id=parent_id AND statement_id = 'X' start WITH id = 0;

DELETE FROM plan_table WHERE statement_id = 'X';

If you see a step in the output with something like "table access by index..." then you're using an index.

"gg" <gianpaolo.giliberto_at_tiscalinet.it> wrote in message news:8vlvj1$rcp$1_at_lacerta.tiscalinet.it...
> Hy,
> we have a table k with a index on 3 fields
> (x,y,z in this order) and
> sometime we have to execute a query like this:
>
> select ..
> from k
> where
> x in (select ... from ....) and
> y=.. and
> z=.....;
>
> Do we use the index with this query ?
>
> Thanks.
> Goodbye
> gianpaolo.giliberto_at_tiscalinet.it
>
>
Received on Fri Nov 24 2000 - 09:35:45 CST