How to find out if an index is not being used in Oracle

Is there a way to find out if an index is not being used in oracle.??
tiger20Asked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
sbenyoConnect With a Mentor Commented:
Yes.
You should run an explain plan on the query you want to check.
Explain plan shows you how oracle executes your query and what indexes are in use in the query.

To do this:

1) connect using SqlPlus.

2) run the script:

$ORACLE_HOME/rdbms/admin/utlxplan.sql

This creates the explain plan table used by oracle to create the explain plan.

2) in sqlplus:
   sqlplus>set autotrace on
   
3) Now run the query. In the explain plain oracle shows you can see if indexes are used or not.

Sample explain:

Execution Plan
----------------------------------------------------------
   0   SELECT STATEMENT Optimizer=FIRST_ROWS (Cost=1 Card=82 Bytes=
    164)

   1 0   TABLE ACCESS (FULL) OF 'DUAL' (Cost=1 Card=82 Bytes=164)

This show a table accessed without indexes (FULL).

You can also use sql trace to do that. If you want to know about it tell me.
0
 
tiger20Author Commented:
Answer accepted
0
 
tiger20Author Commented:
Cheers sbenyo that worked a treat.... Here the points .... enjoy :-)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.