1.2　選擇性（SELECTIVITY）

基數與總行數的比值再乘以100%就是某個列的選擇性。

在進行SQL優化的時候，單獨看列的基數是沒有意義的，基數必須對比總行數才有實際意義，正是因為這個原因，我們才引出了選擇性這個概念。

下面我們查看test表各個列的基數與選擇性，為了查看選擇性，必須先收集統計信息。關于統計信息，我們在第2章會詳細介紹。下面的腳本用于收集test表的統計信息。

SQL> BEGIN
  2    DBMS_STATS.GATHER_TABLE_STATS(ownname          => 'SCOTT',
  3                                  tabname          => 'TEST',
  4                                  estimate_percent => 100,
  5                                  method_opt => 'for all columns size 1',
  6                                  no_invalidate    => FALSE,
  7                                  degree           => 1,
  8                                  cascade          => TRUE);
  9  END;
 10  /

PL/SQL procedure successfully completed.

下面的腳本用于查看test表中每個列的基數與選擇性。

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

COLUMN_NAME       NUM_ROWS CARDINALITY SELECTIVITY HISTOGRAM NUM_BUCKETS
--------------- ---------- ----------- ----------- --------- -----------
OWNER                72462          29         .04 NONE                1
OBJECT_NAME          72462       44236       61.05 NONE                1
SUBOBJECT_NAME       72462         106         .15 NONE                1
OBJECT_ID            72462       72462         100 NONE                1
DATA_OBJECT_ID       72462        7608        10.5 NONE                1
OBJECT_TYPE          72462          44         .06 NONE                1
CREATED              72462        1366        1.89 NONE                1
LAST_DDL_TIME        72462        1412        1.95 NONE                1
TIMESTAMP            72462        1480        2.04 NONE                1
STATUS               72462           1           0 NONE                1
TEMPORARY            72462           2           0 NONE                1
GENERATED            72462           2           0 NONE                1
SECONDARY            72462           2           0 NONE                1
NAMESPACE            72462          21         .03 NONE                1
EDITION_NAME         72462           0           0 NONE                0

15 rows selected.

請思考：什么樣的列必須建立索引呢？

有人說基數高的列，有人說在where條件中的列。這些答案并不完美。基數高究竟是多高？沒有和總行數對比，始終不知道有多高。比如某個列的基數有幾萬行，但是總行數有幾十億行，那么這個列的基數還高嗎？這就是要引出選擇性的根本原因。

當一個列選擇性大于20%，說明該列的數據分布就比較均衡了。測試表test中object_name、object_id的選擇性均大于20%，其中object_name列的選擇性為61.05%?，F在我們查看該列數據分布（為了方便展示，只輸出前10行數據的分布情況）。

SQL> select *
  2    from (select object_name, count(*)
  3            from test
  4           group by object_name
  5           order by 2 desc)
  6   where rownum <= 10;

OBJECT_NAME          COUNT(*)
------------------ ----------
COSTS                      30
SALES                      30
SALES_CHANNEL_BIX          29
COSTS_TIME_BIX             29
COSTS_PROD_BIX             29
SALES_TIME_BIX             29
SALES_PROMO_BIX            29
SALES_PROD_BIX             29
SALES_CUST_BIX             29
DBMS_REPCAT_AUTH            5

10 rows selected.

由上面的查詢結果我們可知，object_name列的數據分布非常均衡。我們查詢以下SQL。

select * from test where object_name=:B1;

不管object_name傳入任何值，最多返回30行數據。

什么樣的列必須要創建索引呢？當一個列出現在where條件中，該列沒有創建索引并且選擇性大于20%，那么該列就必須創建索引，從而提升SQL查詢性能。當然了，如果表只有幾百條數據，那我們就不用創建索引了。

下面拋出SQL優化核心思想第一個觀點：只有大表才會產生性能問題。

也許有人會說：“我有個表很小，只有幾百條，但是該表經常進行DML，會產生熱點塊，也會出性能問題。”對此我們并不想過多地討論此問題，這屬于應用程序設計問題，不屬于SQL優化的范疇。

下面我們將通過實驗為大家分享本書第一個全自動優化腳本。

抓出必須創建索引的列（請讀者對該腳本適當修改，以便用于生產環境）。

首先，該列必須出現在where條件中，怎么抓出表的哪個列出現在where條件中呢？有兩種方法，一種是可以通過V$SQL_PLAN抓取，另一種是通過下面的腳本抓取。

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

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

下面是實驗步驟。

我們首先運行一個查詢語句，讓owner與object_id列出現在where條件中。

SQL> select object_id, owner, object_type
  2    from test
  3   where owner = 'SYS'
  4     and object_id < 100
  5     and rownum <= 10;

 OBJECT_ID OWNER                OBJECT_TYPE
---------- -------------------- -----------
        20 SYS                  TABLE
        46 SYS                  INDEX
        28 SYS                  TABLE
        15 SYS                  TABLE
        29 SYS                  CLUSTER
         3 SYS                  INDEX
        25 SYS                  TABLE
        41 SYS                  INDEX
        54 SYS                  INDEX
        40 SYS                  INDEX

10 rows selected.

其次刷新數據庫監控信息。

SQL> begin
  2    dbms_stats.flush_database_monitoring_info;
  3  end;
  4  /

PL/SQL procedure successfully completed.

然后我們查看test表有哪些列出現在where條件中。

SQL> select r.name owner, o.name table_name, c.name column_name
  2    from sys.col_usage$ u, sys.obj$ o, sys.col$ c, sys.user$ r
  3   where o.obj# = u.obj#
  4     and c.obj# = u.obj#
  5     and c.col# = u.intcol#
  6     and r.name = 'SCOTT'
  7     and o.name = 'TEST';

OWNER      TABLE_NAME COLUMN_NAME
---------- ---------- ------------------------------
SCOTT      TEST       OWNER
SCOTT      TEST       OBJECT_ID

接下來我們查詢出選擇性大于等于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;

OWNER      TABLE_NAME COLUMN_NAME   SELECTIVITY
---------- ---------- ------------- -----------
SCOTT      TEST       OBJECT_NAME         61.05
SCOTT      TEST       OBJECT_ID             100

最后，確保這些列沒有創建索引。

SQL> select table_owner, table_name, column_name, index_name
  2    from dba_ind_columns
  3   where table_owner = 'SCOTT'
  4     and table_name = 'TEST';
未選定行

把上面的腳本組合起來，我們就可以得到全自動的優化腳本了。

SQL> select owner,
  2         column_name,
  3         num_rows,
  4         Cardinality,
  5         selectivity,
  6         'Need index' as notice
  7    from (select b.owner,
  8                 a.column_name,
  9                 b.num_rows,
 10                 a.num_distinct Cardinality,
 11                 round(a.num_distinct / b.num_rows * 100, 2) selectivity
 12            from dba_tab_col_statistics a, dba_tables b
 13           where a.owner = b.owner
 14             and a.table_name = b.table_name
 15             and a.owner = 'SCOTT'
 16             and a.table_name = 'TEST')
 17   where selectivity >= 20
 18     and column_name not in (select column_name
 19                               from dba_ind_columns
 20                              where table_owner = 'SCOTT'
 21                                and table_name = 'TEST')
 22     and column_name in
 23         (select c.name
 24            from sys.col_usage$ u, sys.obj$ o, sys.col$ c, sys.user$ r
 25           where o.obj# = u.obj#
 26             and c.obj# = u.obj#
 27             and c.col# = u.intcol#
 28             and r.name = 'SCOTT'
 29             and o.name = 'TEST');

OWNER      COLUMN_NAME     NUM_ROWS CARDINALITY SELECTIVITY NOTICE
---------- ------------- ---------- ----------- ----------- ----------
SCOTT      OBJECT_ID          72462       72462         100 Need index