抓出需要收集直方图的列

此脚本依赖统计信息。

当一个表比较大，列选择性低于 5%，而且列出现在 where 条件中，为了防止优化器估算 Rows 出现较大偏差，我们需要对这种列收集直方图。以下脚本抓出 Scott 账户下，表总行数大于 5 万行、列选择性低于 5% 并且列出现在 where 条件中的表以及列信息。

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'
   and round(a.num_distinct / b.num_rows * 100, 2) < 5
   and num_rows > 50000
   and (a.table_name, a.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 万行，owner 列选择性小于 5%，而且出现在 where 条件中，通过以上脚本我们可以将其抓出。

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'
     and round(a.num_distinct / b.num_rows * 100, 2) < 5
     and num_rows > 50000
     and (a.table_name, a.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            OWNER                73020          29         .04