抓出必须创建索引的列

此脚本依赖统计信息。

当一个表比较大，列选择性超过 20%，列出现在 where 条件中并且没有创建索引，我们可以对该列创建索引从而提升 SQL 查询性能。以下脚本抓出 Scott 账户下表总行数大于 5 万行、列选择性超过 20%、列出现在 where 条件中并且没有创建索引。

select owner,
       table_name,
       column_name,
       num_rows,
       Cardinality,
       selectivity
  from (select a.owner,
               a.table_name,
               a.column_name,
               b.num_rows,
               a.num_distinct Cardinality,
               round(a.num_distinct / b.num_rows * 100, 2) selectivity
          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')
 where selectivity >= 20
   and num_rows > 50000
   and (table_name, column_name) not in
       (select table_name, column_name
          from dba_ind_columns
         where table_owner = 'SCOTT' and column_position=1)
   and (table_name, column_name) in
       (select o.name, 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');

在 Scott 账户中，test 表总行数大于 5 万行，有两个列出现在 where 条件中，选择性大于 20%，而且没有创建索引，我们通过以上脚本将其抓出。

select owner,
         table_name,
         column_name,
         num_rows,
         Cardinality,
         selectivity
    from (select a.owner,
                 a.table_name,
                 a.column_name,
                 b.num_rows,
                 a.num_distinct Cardinality,
                 round(a.num_distinct / b.num_rows * 100, 2) selectivity
            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')
   where selectivity >= 20
     and num_rows > 50000
     and (table_name, column_name) not in
         (select table_name, column_name
            from dba_ind_columns
           where table_owner = 'SCOTT' and column_position=1)
     and (table_name, column_name) in
         (select o.name, 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');

OWNER    TABLE_NAME      COLUMN_NAME       NUM_ROWS CARDINALITY SELECTIVITY
-------- --------------- --------------- ---------- ----------- -----------
SCOTT    TEST            OBJECT_ID            73020       73020         100
SCOTT    TEST            OBJECT_NAME          73020       41002       56.15