【TUNE_ORACLE】列出可以建立組合索引的SQL(回表再過濾選擇性高的列)的SQL參考

實驗環境

搭建平臺：VMware Workstation

OS：RHEL 6.10

Grid&DB：Oracle 11.2.0.4

SQL參考

select a.sql_id,

       a.sql_text,

       f.table_name,

       c.size_mb,

       e.column_name,

       round(e.num_distinct / f.num_rows * 100, 2) selectivity

  from v$sql a,

       v$sql_plan b,

       (select owner, segment_name, sum(bytes / 1024 / 1024) size_mb

          from dba_segments

         group by owner, segment_name) c,

       dba_tab_col_statistics e,

       dba_tables f

 where a.sql_id = b.sql_id

   and a.child_number = b.child_number

   and b.object_owner = c.owner

   and b.object_name = c.segment_name

   and e.owner = f.owner

   and e.table_name = f.table_name

   and b.object_owner = f.owner

   and b.object_name = f.table_name

   and instr(b.filter_predicates, e.column_name) > 0

   and (e.num_distinct / f.num_rows) > 0.1  --比例要超過10%才算選擇性高

   and c.owner = 'TEST'

   and b.operation = 'TABLE ACCESS'

   and b.options = 'BY INDEX ROWID'  -- TABLE ACCESS  BY INDEX ROWID

   and e.owner = 'TEST'

 order by 4 desc;