Check if the index created in the table is being used or not in oracle

By Santhosh N

You could check if the index created on the table is being used or not by monitoring that and queried

We can alter the index created by using this statement to start monitoring..

ALTER INDEX yourIndex_idx MONITORING USAGE;

Once, it is altered, you can query the table, v$object_usage for checking the result

SELECT table_name, index_name, monitoring, used FROM v$object_usage;
and the result is as follows..

TABLE_NAME                     INDEX_NAME                     MON USE
----------------------------
-- ------------------------------ --- ---
T1                           yourIndex_idx                     YES NO


Once, any query is made after this you could check the last column to be YES...

Check if the index created in the table is being used or not in oracle  (2406 Views)