How to know whether a query is using index or not?

最新推荐文章于 2023-12-22 02:23:09 发布

原创最新推荐文章于 2023-12-22 02:23:09 发布 · 166 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#SQL

oracle 专栏收录该内容

13 篇文章

订阅专栏

本文介绍了一种在数据库中监控索引使用情况的方法。通过设置索引的 MONITORING USAGE 属性，可以查询 v$object_usage 视图来检查索引是否被应用程序使用。文章通过示例展示了如何创建和修改索引来启用或禁用此监控功能。

You can use the index monitoring feature to check if indexes are used by an application or not. If u set the MONITORING USAGE property for an index, you can query the v$object_usage to see if the index is being used or not. Here is an example:

sys@BBK> CREATE INDEX t1_idx ON t1(c1);

Index created.

sys@BBK> ALTER INDEX t1_idx MONITORING USAGE;

Index altered.

sys@BBK> SELECT table_name, index_name, monitoring, used FROM v$object_usage;

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

sys@BBK> SELECT * FROM t1 WHERE c1 = 1;

no rows selected

sys@BBK> SELECT table_name, index_name, monitoring, used FROM v$object_usage;

TABLE_NAME INDEX_NAME MON USE
------------------------------ ------------------------------ --- ---
T1 T1_IDX YES YES

sys@BBK> ALTER INDEX t1_idx NOMONITORING USAGE;

Index altered.

sys@BBK> SELECT table_name, index_name, monitoring, used FROM v$object_usage;

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

sys@BBK>

OR use the following way;

sql> set autot on exp
sql> SELECT * FROM t1 WHERE c1 = 1;