【TUNE_ORACLE】列出SQL謂詞中需要建立索引的列SQL參考

實驗環境

搭建平臺：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');