Oracle最佳化器要給出一個最優的執行計劃，應該也必須知道sql語句中所引用的底層物件的詳細資訊，其中比較重要的概念就是：選擇率和基數。
選擇率(selectivity)和基數(cardinality).官方文件如下：
SELECTIVITY:This measure represents a fraction of rows from a row set. The selectivity is tied to a query predicate, such as last_name='Smith', or a combination of predicates.
Cardinality：This measure represents the number of rows in a row set.
翻譯成中文：a)選擇率就是從一個行的集合中返回行數所佔的比率 b)基數就是從行集合中返回的預估行數
若相關表沒有直方圖，選擇率就簡單的等於DENSITY，cardinality基數=selectivity選擇率 * number of rows 表的行數，下面透過具體的實驗進行說明：

點選(此處)摺疊或開啟

drop table t;
create table t as select rownum all_distinct,10000 skew from dual connect by level<=10000;
update t set skew=all_distinct where rownum<=10;
commit;
select skew,count(*) from t group by skew order by skew;
SKEW COUNT(*)
---------- ----------
1 1
2 1
3 1
4 1
5 1
6 1
7 1
8 1
9 1
10 1
10000 9990

對錶收集統計資訊但不建立直方圖

點選(此處)摺疊或開啟

exec dbms_stats.gather_table_stats(USER,'T',method_opt=>'for all columns size 1');
SELECT COLUMN_NAME,NUM_DISTINCT,utl_raw.cast_to_number(LOW_VALUE),utl_raw.cast_to_number(HIGH_VALUE),DENSITY,NUM_NULLS FROM user_tab_col_statistics WHERE table_name='T';
COLUMN_NAME NUM_DISTINCT UTL_RAW.CAST_TO_NUMBER(LOW_VALUE) UTL_RAW.CAST_TO_NUMBER(HIGH_VALUE) DENSITY NUM_NULLS
--------------- ------------ --------------------------------- ---------------------------------- ---------- ----------
ALL_DISTINCT 10000 1 10000 .0001 0
SKEW 11 1 10000 .090909091 0

由於上述表沒有收集直方圖，最佳化器無法知道列值的具體傾斜情況，因此選擇率就簡單的等於DENSITY(1/NUM_DISTINCT)。下面來檢視一下基數評估的情況：

點選(此處)摺疊或開啟

explain plan for select * from t1 where skew=1;
SQL> SELECT * FROM table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 1601196873
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 909 | 6363 | 7 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T | 909 | 6363 | 7 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
1 - filter("SKEW"=1)

基數評估為909，怎麼計算出來的呢。
選擇率=DENSITY=0.090909091，num of rows=10000，因此cardinality基數=10000*0.090909091=909
但實際情況呢？

點選(此處)摺疊或開啟

SQL> select count(*) from t where skew=1;
COUNT(*)
----------
1

表中skew=1的行數僅為1，評估的基數為909，為什麼會出現這種情況，因為skew列存在嚴重的傾斜，且表對該列也沒有收集直方圖，造成評估錯誤。下面對錶收集直方圖看看基數

點選(此處)摺疊或開啟

exec dbms_stats.gather_table_stats(user,'T',method_opt=>'for columns skew size 11');
SQL> explain plan for select * from t where skew=1;
Explained.
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 1601196873
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 7 | 7 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T | 1 | 7 | 7 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
1 - filter("SKEW"=1)

可以看到，在收集直方圖後基數的評估為1，和實際情況相符。
只有統計資訊準確，資料庫才能生成理想的執行計劃。

選擇率(selectivity)與基數(cardinality)

相關文章