实验环境

搭建平台：VMware Workstation

OS：RHEL 6.10

Grid&DB：Oracle 11.2.0.4

SQL参考

--******该脚本依赖统计信息！！必须先收集统计信息！！******

注：

统计收集方法详见： http://blog.itpub.net/69992972/viewspace-2784605/

--1. 只判断出现在SQL谓词条件中的列是否需要创建索引

select owner,

       column_name,

       num_rows,

       cardinality,

       selectivity,

       'Need index' as notice

  from (select b.owner,

               a.column_name,

               b.num_rows,

               a.num_distinct cardinality,

               round(a.num_distinct / b.num_rows * 100, 2) selectivity

          from dba_tab_col_statistics a, dba_tables b

         where a.owner = b.owner

           and a.table_name = b.table_name

           and a.owner = 'TEST'

           and a.table_name = 'TAB')

 where selectivity >= 20

   and column_name not in

       (select column_name

          from dba_ind_columns

         where table_owner = 'TEST'

           and table_name = 'TAB')

---------------- 出现在where条件中(需要提前刷新监控信息dbms_stats.flush_database_monitoring_info)：

   and column_name in

       (select c.name

          from sys.col_usage$ u, sys.obj$ o, sys.col$ c, sys.user$ r

         where o.obj# = u.obj#

           and c.obj# = u.obj#

           and c.col# = u.intcol#

           and r.name = 'TEST'

           and o.name = 'TAB');

--2. 出现在where条件中，选择性大于20%，总行数大于5W的没有创建索引的列

select owner,

       table_name,

       column_name,

       num_rows,

       cardinality,

       selectivity,

       'Need index' as notice

  from (select a.owner,

               a.table_name,

               a.column_name,

               b.num_rows,

               a.num_distinct cardinality,

               round(a.num_distinct / b.num_rows * 100, 2) selectivity

          from dba_tab_col_statistics a, dba_tables b

         where a.owner = b.owner

           and a.table_name = b.table_name

           and a.owner = 'TEST')

 where selectivity >= 20

   and num_rows > 50000

   and (table_name, column_name) not in

       (select table_name, column_name

          from dba_ind_columns

         where table_owner = 'TEST'

           and column_position = 1)

---------------- 出现在where条件中(需要提前刷新监控信息dbms_stats.flush_database_monitoring_info)：

   and (table_name, column_name) in

       (select o.name, c.name

          from sys.col_usage$ u, sys.obj$ o, sys.col$ c, sys.user$ r

         where o.obj# = u.obj#

           and c.obj# = u.obj#

           and c.col# = u.intcol#

           and r.name = 'TEST');