查看必须创建索引的列 & 查看可以创建组合索引的SQL(回表只访问少数字段)

查看必须创建索引的列

以下脚本依赖统计信息的准确性：

select column_name,
       num_rows,
       Cardinality,
       selectivity,
       histogram,
       num_buckets, 'Consider create index on this column' as notice
  from (select a.column_name,
               b.num_rows,
               a.num_distinct Cardinality,
               round(a.num_distinct / b.num_rows * 100, 2) selectivity,
               a.histogram,
               a.num_buckets
          from dba_tab_col_statistics a, dba_tables b
         where a.owner = b.owner
           and a.table_name = b.table_name
           and a.owner = 'SCOTT'
           and a.table_name = 'TEST')
 where selectivity >= 10
   and column_name not in (select column_name
                             from dba_ind_columns
                            where table_owner = 'SCOTT'
                              and table_name = 'TEST')
   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 = 'SCOTT'
           and o.name = 'TEST');

 

查看可以创建组合索引的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 = 'SCOTT'
   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;