实验环境
搭建平台：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');
