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