Determining Whether a Query Uses an Index
DBAs and developers often wonder if the database is using a certain index. Your explain plans may indicate the use of the index, but you may want to make sure that the database is actually using the index. You can easily track the usage of an index by enabling the monitoring of index usage in your database. By default, Oracle Database doesn't monitor index usage. You can make the database monitor an index by altering the index you're interested in with the monitoring usage clause, as shown here:
SQL> alter index employees_idx1 monitoring usage;
Index altered.
SQL>
Once you turn index monitoring on, the database tracks the usage of the index EMPLOYEES_IDX1. You can query the V$OBJECT_USAGE view to see ...
Get Expert Indexing in Oracle Database 11g: Maximum Performance for Your Database now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.
