【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,

       d.table_name,

       REGEXP_COUNT(b.projection, ']') || '/' || d.column_cnt column_cnt,

       c.size_mb,

       b.FILTER_PREDICATES filter

  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,

       (select owner, table_name, count(*) column_cnt

          from dba_tab_cols

         group by owner, table_name) d

 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 b.object_owner = d.owner

   and b.object_name = d.table_name

   and c.owner = 'TEST'

    and b.operation = 'TABLE ACCESS'

   and b.options = 'BY INDEX ROWID'

   and REGEXP_COUNT(b.projection, ']') / d.column_cnt < 0.25

 order by 5 desc;