Oracle 選擇性(Selectivity)

先執行下面的存儲過程，刷新數據庫監控信息。

begin
  dbms_stats.flush_database_monitoring_info;
end;

運行完上面的命令之後，再運行下面的查詢語句就可以查詢出哪個表的哪個列出現在where條件中。

select r.name owner,
       o.name table_name,
       c.name column_name,
       equality_preds, ---等值過濾
       equijoin_preds, ---等值JOIN 比如where a.id=b.id
       nonequijoin_preds, ----不等JOIN
       range_preds, ----範圍過濾次數 > >= < <= between and
       like_preds, ----LIKE過濾
       null_preds, ----NULL 過濾
       timestamp
  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';

接下來我們查詢出選擇性大於等於20%的列。

SQL> select a.owner,
  2         a.table_name,
  3         a.column_name,
  4         round(a.num_distinct / b.num_rows * 100, 2) selectivity
  5    from dba_tab_col_statistics a, dba_tables b
  6   where a.owner = b.owner
  7     and a.table_name = b.table_name
  8     and a.owner = 'SCOTT'
  9     and a.table_name = 'TEST'
 10     and a.num_distinct / b.num_rows >= 0.2;