Re: Is my query using index

kumar_kmk_at_hotmail.com (Kumar) wrote in message news:<bce41b15.0209291957.520c21b0_at_posting.google.com>...
> If there is a index created on a table, and a query is run against the table
> how do I know if the index is being used or not.

What version are you using.
In 9i it is possible you to monitor indexes I am not sure about earlier versions.
alter the index and turn monitoring on;

alter index "index_name" monitoring usage; **Run your query.**
Query the v$object_usage question and a yes in the used column indicates the index is being used.

desc v$object_usage
index_name
table_name,
monitoring,
used,
start_monitoring
end_monitoring

When you are done issue the alter index nomonitoring usage Be warned you may not be able to tell when an index was used or how many times it was used.Also switching on monitoring wipes out all info in v$object_usage.
This means till you run your query , v$object_usage after issuing monitoring usage will have no values
hth
Michael Tubuo Ngong(Sr DBA) Received on Mon Sep 30 2002 - 16:26:36 CEST