【調優】CBO基礎（四）
單表選擇性
Cardinality
看看單表的集勢：
1，索引欄位：
表t_test_tb5的列的統計資訊如下：
SQL> select a.column_name,a.num_distinct,a.HISTOGRAM
2 from user_tab_col_statistics a where a.table_name = upper('t_test_tb5');
COLUMN_NAME NUM_DISTINCT HISTOGRAM
------------------------------ ------------ ---------------
OWNER 43 FREQUENCY
OBJECT_NAME 48232 HEIGHT BALANCED
SUBOBJECT_NAME 1114 NONE
OBJECT_ID 91960 NONE
DATA_OBJECT_ID 27516 NONE
OBJECT_TYPE 43 FREQUENCY
CREATED 2534 NONE
LAST_DDL_TIME 2751 NONE
TIMESTAMP 2878 NONE
STATUS 2 NONE
TEMPORARY 2 NONE
COLUMN_NAME NUM_DISTINCT HISTOGRAM
------------------------------ ------------ ---------------
GENERATED 2 NONE
SECONDARY 2 NONE
NAMESPACE 20 NONE
EDITION_NAME 1 NONE
已選擇15行。
查詢這個單表，使用OBJECT_NAME作為條件謂詞：
SQL> select /*+ */* from t_test_tb5 a where a.OBJECT_NAME = 'SYS';
未選定行
執行計劃
----------------------------------------------------------
Plan hash value: 169354153
----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4 | 408 | 49 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T_TEST_TB5 | 4 | 408 | 49 (0)| 00:00:01 |
|* 2 | INDEX SKIP SCAN | IND_TEST_TB5_2 | 4 | | 45 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("A"."OBJECT_NAME"='SYS')
filter("A"."OBJECT_NAME"='SYS')
統計資訊
----------------------------------------------------------
1 recursive calls
0 db block gets
163 consistent gets
162 physical reads
0 redo size
1124 bytes sent via SQL*Net to client
405 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processed
SQL>
select count(*) from t_test_tb5 a ;-- 183014
注意到幾個條件：
單表查詢
查詢欄位建立了索引
查詢條件運算子是 “＝”
這裡的rows就是cardinality，使用plsql dev也可以看到，這個值是4，計算方法是這樣的：
Cardinality ＝ rows/column’s num_distinct = ceil( 183014/48232) = 4
再來看看這幾個條件的實質是什麼：
select /*+ */* from t_test_tb5 a where a.owner = 'SYS';
顯示的cardinality為58947，這個owner上面也有索引，也是單表，也是＝，但是並不能適合上面的公式，仔細檢視一下相關資訊
select * from DBA_TAB_COL_STATISTICS a where a.table_name = upper('t_test_tb5');
select * from dba_tab_histograms a where a.table_name = upper('t_test_tb5') and a.COLUMN_NAME = 'OWNER';
select * from dba_tab_histograms a where a.table_name = upper('t_test_tb5') and a.COLUMN_NAME = 'OBJECT_NAME';
可以看出，在owner欄位上的直方圖是頻度直方圖(FREQUENCY)，而在object_name欄位上的直方圖是高度平衡的直方圖（HEIGHT BALANCED）。
關於頻度直方圖和高度直方圖：
一般來說頻度直方圖的buckets少於高度直方圖，用於在num_distinct值遠小於num_rows的情況，例如owner的num_distinct只有43，但是num_rows有183014，而且不同owner值的行數非常不平衡，頻度直方圖很好的記錄了這個特性；而高度直方圖則適用於欄位下重複的情況並不算太多，並且資料傾斜也不算太嚴重。現在可以再次推測：
對於單表單列索引訪問，且欄位上為高度直方圖的情況，card值可以由前面所述公式得出。
而頻度直方圖的cardinality則直接反映了條件值選取的行數。
對於高度直方圖來說，> < <>運算子也反映了區間內的行數，所以在統計資訊正確完全的情況下，通過觀察card值大概就可以知道運算中或者運算結果的行數了。
同樣的，其他的運算子><<>也都能反映事實的資料行數，如：
select /*+ */* from t_test_tb5 a where a.owner <> 'SYS';
這個的card值應該就是所有行減去前面＝'SYS'的行數值。
2，非索引欄位
＝號的情況：
select /*+ */count(*) from t_test_tb5 a where a.subobject_name is not null;--28920
num_distinct值為1114
select ceil(28920/1114) from dual;――26
可以看到card值就是26：
SQL> select /*+ */* from t_test_tb5 a where a.SUBOBJECT_NAME = 'ASD';
未選定行
執行計劃
----------------------------------------------------------
Plan hash value: 4107245802
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 26 | 2652 | 756 (1)| 00:00:10 |
|* 1 | TABLE ACCESS FULL| T_TEST_TB5 | 26 | 2652 | 756 (1)| 00:00:10 |
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("A"."SUBOBJECT_NAME"='ASD')
統計資訊
----------------------------------------------------------
1 recursive calls
0 db block gets
2726 consistent gets
0 physical reads
0 redo size
1124 bytes sent via SQL*Net to client
405 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processed
SQL>
而><<> >= <= 等符號的card值演算法也很容易推算出來。
顯然<= 的值為<的值加上=的值，>=也類似，下面看看<和>的不同情況是如何計算card值的：
先看資料情況：
select /*+ */max(SUBOBJECT_NAME) from t_test_tb5 a where a.SUBOBJECT_NAME < 'Z';--WRH$_WAITST_2054048211_1211
select /*+ */count(SUBOBJECT_NAME) from t_test_tb5 a where a.SUBOBJECT_NAME < 'Z';--28920 即distinct值
select /*+ */min(SUBOBJECT_NAME) from t_test_tb5 a where a.SUBOBJECT_NAME < 'Z';--$VSN_1
對於查詢：
select /*+ */* from t_test_tb5 a where a.SUBOBJECT_NAME < 'A';
card值是16259
大致即為：
select ceil((28920/(ascii('W')-ascii('$'))) * (ascii('A')-ascii('$'))) from dual;--16445
可以用一個精確的例子來驗證一下這個演算法是否正確：
建立一個表，其中一個欄位的值從a-z不重複，各有1行，然後分別測試card值：
create table t_test_tb9 (name varchar2(10));
begin
for i in 0..25 loop
insert into t_test_tb9 select chr(97+i) from dual;
end loop;
end;
/
select * from t_test_tb9 a where a.name < 'h';--8
完全符合前面推論。
然後為了證明oracle在沒有索引沒有直方圖時對資料分佈的是否未知，可以插入1000條值為h的name值來測試：
begin
for i in 0..999 loop
insert into t_test_tb9 select chr(104) from dual;
end loop;
end;
/
select * from t_test_tb9 a where a.name < 'i';--card 1008
貌似很神奇，oracle知道插入了1000行列值為h的行，這個是10g11g的一些新的特性，暫且不分析，但是已經知道的是，前面的列統計資訊和直方圖資訊都不會有這個列的資訊的。
接下來再刪除h值的行：
delete from t_test_tb9 a where a.name = 'h';
再看一下前面一句話的card
select * from t_test_tb9 a where a.name < 'i';--card 1008
沒有改變，因為delete並沒有改變table的hwm，這個card值其實就是不合實際的，所以需要收集物件統計資訊，讓oracle優化器知道已經delete。
begin
dbms_stats.gather_table_stats(user,'t_test_tb9');
end;
/
select * from t_test_tb9 a where a.name < 'i'――card 8
收集表的預設統計資訊，會在下面看到這些值：
select * from user_tab_col_statistics a where a.table_name = upper('t_test_tb9');
select * from user_tab_cols a where a.TABLE_NAME = upper('t_test_tb9');
select * from user_tab_columns a where a.TABLE_NAME = upper('t_test_tb9');
select * from user_tab_histograms a where a.TABLE_NAME = upper('t_test_tb9');
通過這些資訊oracle有了足夠的資訊來生成最合實際的執行計劃，最能體現真是代價的cost和card值。
前面已經推導過=號的card，再來看看in list操作的card值：
select * from t_test_tb9 a where a.name in ( 'h','i','j','k');--4
in ( 'h','i','j','k')操作相當於 = 'h' r ='i' r ='j' r ='k'
比較好理解，再看看not in:
select * from t_test_tb9 a where a.name not in ( 'h','i','j','k')--21
這個例子非常清晰，當然card值就是num_rows – in_list_count
看看單表多個謂詞的情況：
create table t_test_tb10 as select * from t_test_tb9 a ;
alter table t_test_tb10 add (id number);
update t_test_tb10 a set a.id = rownum ;
select * from t_test_tb10 a where a.id < 1000 and a.name in ('a','h')
也很容易得出card值，很簡單的演算法，cbo裡面的演算法並不都是很複雜的。
取決於是否擁有統計資訊，如果統計資訊完全，則cbo優化器能很好的估算card值。
因此sql語句中各個謂詞的選擇性，即給語句帶來的card值大小應該有所瞭解，很多開發人員在拿到需求的時候可能就已經大致曉得這個sql的資料量，以及給系統帶來的開銷值。
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/16179598/viewspace-714026/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 【調優】CBO基礎
- 【調優】CBO基礎（八）
- 【調優】CBO基礎（六）
- 【調優】CBO基礎（五）
- 【調優】CBO基礎（三）
- 【調優】CBO基礎（二）
- 【效能調優】效能測試、分析與調優基礎
- 【sql調優】繫結變數與CBOSQL變數
- Java 9 中的 GC 調優基礎JavaGC
- Java 9中的GC調優基礎JavaGC
- Redis 基礎、高階特性與效能調優Redis
- Redis基礎、高階特性與效能調優Redis
- cbo心得(選擇率,基數,直方圖)(四)直方圖
- Presto記憶體調優及原理（基礎篇）REST記憶體
- 【基礎篇索引】索引基礎（四）索引
- 面試之 Redis 基礎、高階特性與效能調優面試Redis
- 逆向基礎（四）
- Dart基礎（四）Dart
- java基礎（四）Java
- 【CBO】基於成本優化器的基本原則（二）優化
- 【CBO】基於成本優化器的基本原則（一）優化
- 前端基礎入門四（JavaScript基礎）前端JavaScript
- 測試基礎（四）Jmeter基礎使用JMeter
- JVM效能調優與實戰基礎理論篇-下JVM
- Kotlin基礎四Kotlin
- 儲存基礎（四）
- Oracle優化器(RBO與CBO)Oracle優化
- Spark SQL 效能優化再進一步 CBO 基於代價的優化SparkSQL優化
- OC基礎-(四)KVC、KVO
- Struts2 基礎（四）
- SQL Server調優系列基礎篇（子查詢運算總結）SQLServer
- SQL優化基礎SQL優化
- 袋鼠雲數棧基於CBO在Spark SQL優化上的探索SparkSQL優化
- CBO Cost Formulas基於成本優化器的成本計算公式大全ORM優化公式
- 基於CBO優化器謂詞選擇率的計算方法優化
- SQL Server調優系列基礎篇（並行運算總結篇二）SQLServer並行
- Java面試題-基礎篇四Java面試題
- JavaScript 基礎(四) – HTML DOM EventJavaScriptHTML