抓出索引被哪些 SQL 引用

此脚本不依赖统计信息。

有时开发人员可能会胡乱建立一些索引，但是这些索引在数据库中可能并不会被任何一个 SQL 使用。这样的索引会增加维护成本，我们可以将其删掉。下面脚本查询 SQL 使用哪些索引。

select a.sql_text, a.sql_id, b.object_owner, b.object_name, b.object_type
  from v$sql a, v$sql_plan b
 where a.sql_id = b.sql_id
   and a.child_number = b.child_number
   and object_owner = 'SCOTT'
   and object_type like '%INDEX%'
order by 3,4,5;

我们在 Scott 账户中运行下面 SQL 并且查看执行计划。

select * from t where object_id<100;

98 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 827754323
-------------------------------------------------------------------------------------
| Id | Operation                   | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|  0 | SELECT STATEMENT            |        |    91 |  8827 |     4   (0)| 00:00:01 |
|  1 |  TABLE ACCESS BY INDEX ROWID| T      |    91 |  8827 |     4   (0)| 00:00:01 |
|* 2 |   INDEX RANGE SCAN          | IDX_ID |    91 |       |     2   (0)| 00:00:01 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("OBJECT_ID"<100)

我们通过脚本将它抓出。

 select a.sql_text, a.sql_id, b.object_owner, b.object_name, b.object_type
    from v$sql a, v$sql_plan b
   where a.sql_id = b.sql_id
     and a.child_number = b.child_number
     and object_owner = 'SCOTT'
     and object_type like '%INDEX%'
  order by 3,4,5;

SQL_TEXT                         SQL_ID       OBJECT_OWNER OBJECT_NAME    OBJECT_TYPE
-------------------------------- ------------ ------------ -------------- -----------
select * from t where object_id<100   0nvp2p03p06k4  SCOTT         IDX_ID            INDEX