抓出必须创建索引的列

抓出必须创建索引的列

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

 

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');