CBO計算與資料型別的選擇（兼談日期、字元、數字三種型別的直方圖生成原理和使用）
看TOM的PPT中有一份演示，其實在LEWIS的COST BASED中也有類似的例子，就是說如果我一個欄位是時間型別的，那我可以有很多種變通的儲存方式。比如2010年12月1日，可以儲存成DATE型別； 也可以儲存成字串20100101；也可以儲存成NUMBER型別的20100101。這些儲存方式也都可以用來做大小比較，查詢等等，有些開發人員確 實有把DATE轉化成NUMBER儲存的習慣。下面就借用TOM的例子來看看相同的資料，採用不同的型別儲存的時候，對最佳化器以及執行計劃（查詢效率）的 影響。[@more@]
首先建立一個表，並插入些測試資料（下面程式碼摘自TOM PPT）：
ops$tkyte%ORA11GR2> create table t ( str_date, date_date, number_date, data )
2 as
3 select to_char( dt+rownum,'yyyymmdd' ) str_date,
4 dt+rownum date_date,
5 to_number( to_char( dt+rownum,'yyyymmdd' ) ) number_date,
6 rpad('*',45,'*') data
7 from (select to_date('01-jan-1995','dd-mon-yyyy') dt
8 from all_objects)
9 order by dbms_random.random
10 /
Table created.
ops$tkyte%ORA11GR2> create index t_str_date_idx on t(str_date);
ops$tkyte%ORA11GR2> create index t_date_date_idx on t(date_date);
ops$tkyte%ORA11GR2> create index t_number_date_idx on t(number_date);
ops$tkyte%ORA11GR2> begin
2 dbms_stats.gather_table_stats
3 ( user, 'T',
4 method_opt=> 'for all indexed columns size 254',
5 cascade=> true );
6 end;
7 /
PL/SQL procedure successfully completed.
這段程式碼是建立一個表，其中有三個欄位，分別是字串表示的日期、數字表示的日期和日期型表示的日期，然後插入些測試資料，並在每個欄位上單獨建立索引，並對索引列收集直方圖資訊。
表中共有記錄數
SQL> select count(*) from t;
COUNT(*)
----------
71600
執行一個簡單的查詢：
SQL> SELECT * FROM T WHERE NUMBER_DATE BETWEEN 20001231 AND 20010101;
STR_DATE DATE_DATE NUMBER_DATE DATA
-------- --------- ----------- ---------------------------------------------
20001231 31-DEC-00 20001231 *********************************************
20010101 01-JAN-01 20010101 *********************************************
Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 258 | 12126 | 202 (1)| 00:00:03 |
|* 1 | TABLE ACCESS FULL| T | 258 | 12126 | 202 (1)| 00:00:03 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("NUMBER_DATE"<=20010101 AND "NUMBER_DATE">=20001231)
表一共7萬多條記錄，查詢只返回兩條資料，應該走索引的效率更高，可是這裡卻走了一個全表掃描，而且可以看到ROWS那裡標識出返回結果有253行，這說明CBO估算的時候出現了嚴重的不準確，下面來看看這個不準確的數字是怎麼計算出來的。
ORACLE收集直方圖的時候，有一個BUCKET的概念，就是前面收集統計資訊的時候指定的for all indexed columns size 254，254表示使用254個BUCKET去收集直方圖，BUCKET的取值範圍為0到254，不指定值的時候預設值為75。直方圖的資訊可以從DBA_TAB_HISTOGRAMS中檢視：
SQL> SELECT *
2 FROM DBA_TAB_HISTOGRAMS
3 WHERE TABLE_NAME = 'T'
4 AND COLUMN_NAME = 'NUMBER_DATE' AND ROWNUM<=2;
OWNER TABLE_NAME COLUMN_NAME ENDPOINT_NUMBER ENDPOINT_VALUE ENDPOINT_ACTUAL_VALUE
------- ------------- ------------- --------------- -------------- ---------------------
SYS T NUMBER_DATE 0 19950121
SYS T NUMBER_DATE 1 19951003
使用254個BUCKET的時候，這裡會有255行資料來表示254個區間，每個區間也就是一個BUCKET，每個BUCKET的區間起始值可以根據ENDPOINT_VALUE來劃定。劃分BUCKET的原則就是等分法，劃完後落到每個BUCKET的記錄總數是相等的。所以，如果某個區間的值比較少，那麼這個BUCKET所跨越的取值區間機會比較大；反過來如果某一個BUCKET中的值非常多，那麼這個BUCKET的取值區間就會窄，從而保證了最終落到每個BUCKET中的記錄總數是相同的。
我們可以使用下面的SQL來檢視BUCKET的分佈情況：
SQL> SELECT ROWNUM TENTH,
2 PREV LOW_VAL,
3 CURR HIGH_VAL,
4 CURR - PREV WIDTH,
5 ROUND(282 / (CURR - PREV), 2) HEIGTH
6 FROM (SELECT ENDPOINT_VALUE CURR,
7 LAG(ENDPOINT_VALUE, 1) OVER(ORDER BY ENDPOINT_NUMBER) PREV
8 FROM DBA_TAB_HISTOGRAMS
9 WHERE TABLE_NAME = 'T'
10 AND COLUMN_NAME = 'NUMBER_DATE')
11 WHERE PREV IS NOT NULL
12 ORDER BY CURR;
13 /
TENTH LOW_VAL HIGH_VAL WIDTH HEIGTH
---------- ---------- ---------- ---------- ----------
1 19950121 19951003 882 0.32
2 19951003 19960621 9618 0.03
3 19960621 19970311 9690 0.03
4 19970311 19971104 793 0.36
5 19971104 19980712 9608 0.03
6 19980712 19990521 9809 0.03
7 19990521 20000316 9795 0.03
8 20000316 20001011 695 0.41
9 20001011 20010706 9695 0.03
10 20010706 20020331 9625 0.03
11 20021226 20030813 9587 0.03
12 20030813 20040718 9905 0.03
13 20040718 20050902 10184 0.03
節約篇幅，只取了BUCKET值從小到大的前幾個BUCKET，其中的TENTH表示第幾個BUCKET，LOW_VAL和HIGH_VAL表示這個BUCKET的最小和最大值，WIDTH表示這個BUCKET的取值跨度，HEIGTH表示BUCKET的高度，也就是平均下來的話，在這個BUCKET中隨便選一個值，那麼這個值佔這個BUCKET中所有的取值的比值，也可以認為是可選擇度。比如第一個BUCKET的HEIGTH為0.03，表示如果在區間19950103到19960621內取100個值的話，平均下來會有3個值是在表T中的。（上面計算公式中的282是表的記錄總數71600/bucket數量的結果，也就是說每個BUCKET中包含了282條記錄。這個BUCKET中一共有9618個取值的可能，但是一共只有282條記錄，所以區間內隨便取一個值的話，這個值可能在表中的機率是0.03）
下面來看看錶的返回的ROWS是如何計算的，公司如下：
ROWS=SELECTIVITY*NUMBER OF ROWS IN A BUCKET=((REQUIRED RANGE)/(HIGH_VAL-LOW_VAL)+2*DENSITY)*NUMBER OF ROWS IN A BUCKET
其中，NUMBER OF ROWS IN A BUCKET就是上面的總記錄數除以收集直方圖時的BUCKET的數量(其中BUCKET的數量可以從DBA_TAB_COLUMNS.NUM_BUCKETS得到)。
REQUIRED RANGE表示的是查詢條件的範圍，如果範圍跨越了多個BUCKET，則要分別計算每個範圍對應的RANGE，並且去跟相應的BUCKET的HIGH_VAL和LOW_VAL做計算，然後再把計算結果全部相加。
HIGH_VAL和LOW_VAL表示查詢範圍所對應的BUCKET的區間值
DENSITY取自DBA_TAB_COLUMNS.DENSITY，如果沒有直方圖的時候，這個值就等於1/COUNT(DISTINCT COL)；有直方圖的時候，這裡的值會有相應的變化。（如果不是範圍查詢而是等值查詢，那麼這裡的ROWS將會是COUNT*DENSITY，也就是表的記錄總數*DENSITY）
根據上面的計算公式，我們來計算下返回的ROWS:
SQL> SELECT *
2 FROM (SELECT ROWNUM TENTH,
3 PREV LOW_VAL,
4 CURR HIGH_VAL,
5 CURR - PREV WIDTH,
6 ROUND(282 / (CURR - PREV), 2) HEIGTH
7 FROM (SELECT ENDPOINT_VALUE CURR,
8 LAG(ENDPOINT_VALUE, 1) OVER(ORDER BY ENDPOINT_NUMBER) PREV
9 FROM DBA_TAB_HISTOGRAMS
10 WHERE TABLE_NAME = 'T'
11 AND COLUMN_NAME = 'NUMBER_DATE')
12 WHERE PREV IS NOT NULL
13 ORDER BY CURR)
14 WHERE LOW_VAL <= 20001231
15 AND HIGH_VAL >= 20010101;
16 /
TENTH LOW_VAL HIGH_VAL WIDTH HEIGTH
---------- ---------- ---------- ---------- ----------
9 20001011 20010706 9695 0.03
上面的查詢只落在一個BUCKET中，那麼：
ROWS=((20010101-20001231)/(20010706-20001011)+2*0.00000142120148373435)*282=258.010941553424
結果近似於258，既然有258條結果返回，那麼相應的走索引然後再回表取資料的COST計算就增加了，導致ORACLE會選擇一個全表掃描來得到結果。
而這個執行計劃就是不正確的，那麼在字元型儲存的日期型別中也有類似的問題，唯一不同的是字元型沒有這麼直觀，它的BUCKET區間是透過字元轉換然後存在HISTOGRAMS的,這個轉換有計算公式的:
1、首先把字串轉換成16進位制，如果轉換後的長度小於15位元組，則右邊用0補齊到15個位元組；如果轉換後長度大於15位元組，則只取前15個位元組
2、將這個16進位制數字轉換成十進位制資料，並只取前15位，然後右邊補齊到36位就可以得到最終的BUCKET的ENDPOINT_VALUE了
公式如下：
SELECT RPAD(SUBSTR(TO_NUMBER(RPAD(UTL_RAW.CAST_TO_RAW('str'), 30, '0'),
'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXX'),
1,
15),
36,
'0')
FROM DUAL;
轉換後，輸入的查詢條件對應的值分別為：
SQL> SELECT RPAD(SUBSTR(TO_NUMBER(RPAD(UTL_RAW.CAST_TO_RAW('20001231'), 30, '0'),
2 'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXX'),
3 1,
4 15),
5 36,
6 '0') AS START_DATE,
7 RPAD(SUBSTR(TO_NUMBER(RPAD(UTL_RAW.CAST_TO_RAW('20010101'), 30, '0'),
8 'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXX'),
9 1,
10 15),
11 36,
12 '0') AS END_DATE
13 FROM DUAL
14 ;
START_DATE END_DATE
-------------------------------------------------------------------------------- --------------------------------------------------------------------------------
260592216454272000000000000000000000 260592216762543000000000000000000000
拿這兩個數值去HISTOGRAMS中查詢，結果如下：
SQL> SELECT TENTH, TO_CHAR(LOW_VAL), TO_CHAR(HIGH_VAL), TO_CHAR(WIDTH), HEIGTH
2 FROM (SELECT ROWNUM TENTH,
3 PREV LOW_VAL,
4 CURR HIGH_VAL,
5 CURR - PREV WIDTH,
6 ROUND(282 / (CURR - PREV), 2) HEIGTH
7 FROM (SELECT ENDPOINT_VALUE CURR,
8 LAG(ENDPOINT_VALUE, 1) OVER(ORDER BY ENDPOINT_NUMBER) PREV
9 FROM DBA_TAB_HISTOGRAMS
10 WHERE TABLE_NAME = 'T'
11 AND COLUMN_NAME = 'STR_DATE')
12 WHERE PREV IS NOT NULL
13 ORDER BY CURR)
14 WHERE LOW_VAL <= 260592216454272000000000000000000000
15 AND HIGH_VAL >= 260592216762543000000000000000000000;
TENTH TO_CHAR(LOW_VAL) TO_CHAR(HIGH_VAL) TO_CHAR(WIDTH) HEIGTH
---------- ---------------------------------------- ---------------------------------------- ---------------------------------------- ----------
9 260592216454263000000000000000000000 260592216762572000000000000000000000 308309000000000000000000000 0
可以看到，這個查詢範圍是落在第九個BUCKET中的，而且只落在這一個BUCKET中，可以計算返回的ROWS：
SQL> SELECT ((260592216762543000000000000000000000 -
2 260592216454272000000000000000000000) /
3 (260592216762572000000000000000000000 -
4 260592216454263000000000000000000000) + 2 * .0000139134306345916) * 282
5 FROM DUAL
6 ;
((2605922167625430000000000000
------------------------------
281.973089837272
大約282行，而實際的執行計劃如下：
SQL> SELECT * FROM T WHERE STR_DATE BETWEEN '20001231' AND '20010101';
STR_DATE DATE_DATE NUMBER_DATE DATA
-------- --------- ----------- ---------------------------------------------
20001231 31-DEC-00 20001231 *********************************************
20010101 01-JAN-01 20010101 *********************************************
Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 258 | 12126 | 202 (1)| 00:00:03 |
|* 1 | TABLE ACCESS FULL| T | 258 | 12126 | 202 (1)| 00:00:03 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("STR_DATE"<='20010101' AND "STR_DATE">='20001231')
這當中是有出入的，不知道是否是因為捨去了些資料精度導致的。但是HISTOGRAM中有一列叫ENDPOINT_ACTUAL_VALUE的，裡面存放了實際的沒有經過轉換的原始的字串的上下限，把這個當做NUMBER去計算的話，是能得到精確的返回258行結果的。
但是如果使用日期型，就沒有這個問題了。上面問題的發生主要是因為業務意義上的值是連續的，但是因為型別選擇問題，ORACLE認為這個值是不連續的，這兩個值中間有超級多的可選擇的值，所以導致ORACLE認為這樣一個範圍掃描的成本是很高的。那麼如果正確的使用日期型別來儲存，得到的直方圖如下：
SQL> SELECT ROWNUM TENTH,
2 PREV LOW_VAL,
3 CURR HIGH_VAL,
4 CURR - PREV WIDTH,
5 ROUND(282 / (CURR - PREV), 2) HEIGTH
6 FROM (SELECT ENDPOINT_VALUE CURR,
7 LAG(ENDPOINT_VALUE, 1) OVER(ORDER BY ENDPOINT_NUMBER) PREV
8 FROM DBA_TAB_HISTOGRAMS
9 WHERE TABLE_NAME = 'T'
10 AND COLUMN_NAME = 'DATE_DATE')
11 WHERE PREV IS NOT NULL
12 ORDER BY CURR;
TENTH LOW_VAL HIGH_VAL WIDTH HEIGTH
---------- ---------- ---------- ---------- ----------
1 2449739 2449994 255 1.11
2 2449994 2450256 262 1.08
3 2450256 2450519 263 1.07
4 2450519 2450757 238 1.18
5 2450757 2451007 250 1.13
6 2451007 2451320 313 0.9
7 2451320 2451620 300 0.94
8 2451620 2451829 209 1.35
9 2451829 2452097 268 1.05
10 2452097 2452365 268 1.05
11 2452365 2452736 371 0.76
12 2452736 2452956 220 1.28
其中的BUCKET是轉換後的時間，使用的是JULIAN（January 1, 4712 BC開始到現在經過的天數）時間，這種時間格式和我們常見的年月日的轉換方式如下：
SQL> SELECT TO_NUMBER(TO_CHAR(SYSDATE, 'J')) FROM DUAL;
TO_NUMBER(TO_CHAR(SYSDATE,'J')
------------------------------
2455216
上面是轉換當前時間到JULIAN時間，反過來，如果知道一個JULIAN時間，也可以得到年月日時間：
SQL> select to_date(2455216,'J') from dual
2 /
TO_DATE(2455216,'J')
--------------------
2010-1-19
這樣轉換後，20001231和20010101之間的差值將不會是之前的20010101-20001231=8870，而是差值只有1，所以CBO計算返回的ROWS也會非常精確，從而使得ORACLE選擇更準確的執行計劃，下面來驗證下：
SQL> SELECT TO_NUMBER(TO_CHAR(TO_DATE('20001231', 'yyyymmdd'), 'J')) AS START_DATE,
2 TO_NUMBER(TO_CHAR(TO_DATE('20010101', 'yyyymmdd'), 'J')) AS END_DATE
3 FROM DUAL;
START_DATE END_DATE
---------- ----------
2451910 2451911
首先得到兩個日期範圍的取值在HISTOGRAM中對應的值.
SQL> SELECT TENTH, TO_CHAR(LOW_VAL), TO_CHAR(HIGH_VAL), TO_CHAR(WIDTH), HEIGTH
2 FROM (SELECT ROWNUM TENTH,
3 PREV LOW_VAL,
4 CURR HIGH_VAL,
5 CURR - PREV WIDTH,
6 ROUND(282 / (CURR - PREV), 2) HEIGTH
7 FROM (SELECT ENDPOINT_VALUE CURR,
8 LAG(ENDPOINT_VALUE, 1) OVER(ORDER BY ENDPOINT_NUMBER) PREV
9 FROM DBA_TAB_HISTOGRAMS
10 WHERE TABLE_NAME = 'T'
11 AND COLUMN_NAME = 'DATE_DATE')
12 WHERE PREV IS NOT NULL
13 ORDER BY CURR)
14 WHERE LOW_VAL <= 2451910
15 AND HIGH_VAL >= 2451911;
TENTH TO_CHAR(LOW_VAL) TO_CHAR(HIGH_VAL) TO_CHAR(WIDTH) HEIGTH
---------- ---------------------------------------- ---------------------------------------- ---------------------------------------- ----------
9 2451829 2452097 268 1.05
然後可以得到這個值落在了第九個BUCKET中，那麼下面來計算下返回的ROWS:
SQL> SELECT ((2451911 - 2451910) / (2452097 - 2451829) +
2 2 * 0.0000139134306345916) * 282
3 FROM DUAL
4 ;
((2451911-2451910)/(2452097-24
------------------------------
1.06008598084806
可以看到返回一行資料，那麼跟實際執行計劃中的對比下：
SQL> SELECT *
2 FROM T
3 WHERE DATE_DATE BETWEEN TO_DATE('20001231', 'yyyymmdd') AND
4 TO_DATE('20010101', 'yyyymmdd');
STR_DATE DATE_DATE NUMBER_DATE DATA
-------- --------- ----------- ---------------------------------------------
20001231 31-DEC-00 20001231 *********************************************
20010101 01-JAN-01 20010101 *********************************************
Execution Plan
----------------------------------------------------------
Plan hash value: 546586007
-----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 47 | 4 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T | 1 | 47 | 4 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | T_DATE_DATE_IDX | 1 | | 2 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("DATE_DATE">=TO_DATE(' 2000-12-31 00:00:00', 'syyyy-mm-dd hh24:mi:ss')
AND "DATE_DATE"<=TO_DATE(' 2001-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
可以看到，手工計算的跟執行計劃中的是一致的。
總結：
1、資料型別的選擇大有學問
2、字元、數字、日期的直方圖計算方式以及直方圖對執行計劃的影響
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/25016/viewspace-1030717/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 【cbo計算公式】Frequenccy 直方圖選擇率(三)公式直方圖
- cbo心得(選擇率,基數,直方圖)(三)直方圖
- CBO各種型別成本計算公式型別公式
- cbo心得(選擇率,基數,直方圖)(一)直方圖
- cbo心得(選擇率,基數,直方圖)(二)直方圖
- cbo心得(選擇率,基數,直方圖)(四)直方圖
- cbo心得(選擇率,基數,直方圖)(五)直方圖
- Mysql資料庫學習（二）：資料型別（數值型別 日期和時間型別 字串型別）MySql資料庫資料型別字串
- 【cbo計算公式】Height-balance直方圖選擇率(四)公式直方圖
- PHP資料型別轉換(字元轉數字,數字轉字元)PHP資料型別字元
- Flutter Key的原理和使用(三) LocalKey的三種型別Flutter型別
- 選擇合適的資料型別資料型別
- 高效能Mysql(第3版)_資料型別的選擇_整數型別MySql資料型別
- Kotlin 資料型別詳解：數字、字元、布林值與型別轉換指南Kotlin資料型別字元
- 如何選擇各種型別資料庫？- Raj型別資料庫
- MySQL 資料型別分類和選擇MySQL 資料型別
- MySQL字元資料型別char與varchar的區別MySql字元資料型別
- 基本資料型別與API引用型別的使用資料型別API
- 資料更改事件的三種型別事件型別
- Oracle基本資料型別儲存格式淺析（三）——日期型別（三）Oracle資料型別
- SQL Server日期資料型別DATE的使用SQLServer資料型別
- 【MySQL資料型別3之--字元型別】MySql資料型別字元
- 日期型別與String型別的轉換型別
- Redis 中三種特殊的資料型別Redis資料型別
- 【BBED】使用bbed 修改日期型別的資料型別
- 強型別語言變數和資料型別的理解變數資料型別
- Oracle基本資料型別儲存格式淺析（三）——日期型別（一）Oracle資料型別
- Oracle基本資料型別儲存格式淺析（三）——日期型別（二）Oracle資料型別
- Oracle基本資料型別儲存格式淺析（三）——日期型別（四）Oracle資料型別
- Redis 三種特殊資料型別Redis資料型別
- 設計表時，如何選擇正確的資料型別資料型別
- 【MySQL資料型別2之--日期時間型別】MySql資料型別
- C語言資料型別和算數運算C語言資料型別
- 【BBED】使用bbed修改字元型別資料字元型別
- 資料型別與底層原理資料型別
- 雲端計算開發教程：Python資料型別之數字Python資料型別
- 關於直方圖中關於字元型別的ENDPOINT_VALUE欄位的轉換直方圖字元型別
- C++ 使用者輸入與資料型別詳解：建立基本計算器及變數型別C++資料型別變數