CBO計算與資料型別的選擇(兼談日期、字元、數字三種型別的直方圖生成原理和使用)

zhang41082發表於2019-03-25



看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/,如需轉載,請註明出處,否則將追究法律責任。

相關文章