Histograms

這一節我們專門來介紹histograms

histograms一般被我們翻譯成為直方圖，實際上當我們統計了histograms資訊之後，user_tab_histograms裡面的資訊確實可以以直方圖的方式展示給我們。而這些資訊描述的就是我們的表的資訊的分佈情況。
我們這一節將會主要介紹什麼是直方圖以及Oracle使用直方圖的一些細節。

和以前一樣，我們的討論還是從一個實驗開始：

1）什麼是histograms

首先還是建立測試表：
execute dbms_random.seed(0)

create table t1
as
with kilo_row as (
select /*+ materialize */
rownum
from all_objects
where rownum <= 1000
)
select
trunc(7000 * dbms_random.normal) normal
from
kilo_row k1,
kilo_row k2
where
rownum <= 1000000
;

這個建表語句沒有什麼特別的，其中一個是使用了所謂的subquery factoring語法，這樣我就不需要單獨建立kilo_row這個中間表，另一個比較主要的就是透過dbms_random來生成表中的隨機數，雖說是隨機生成的資料，但如果大家用過dbms_random的話就會知道我們生成的這些資料分佈是不平均的，你可以用
select normal, count(*) from t1 group by normal;
來看到具體的資料分佈，應該是類似於下圖的：

整個表的資料其實是大致上以0為中心分佈，越靠近出現的次數越多，離0越遠出現機率越小，在我的測試裡，整個取值範圍是在-32003和34660之間。
SQL> select min(normal), max(normal) from t1;

MIN(NORMAL) MAX(NORMAL)
----------- -----------
-32003 34660

現在我們的t1表有一百萬行資料，現在我決定把這些行從小到大排列，然後分為10個組，每組十萬行，並給每一個行分配一個組號，這樣最小的就是組1，最大的就是組10。
我們可以用下面的分析函式實現這一點：

select
normal,
ntile(10) over (order by normal) tenth
from t1
;

這個查詢的output類似與：

NORMAL TENTH
---------- ----------
-11929 1
-5081 2
-4837 3
-4366 4
-2348 5
-1316 6
-375 7
-244 8
4290 9
............
-8966 2
-8966 2
-8966 2
-8966 2
-8966 2
-8966 2
-8966 2
-8966 2
-8966 2
............
............
............
............
............
NORMAL TENTH
---------- ----------
0 10
0 10
0 10
0 10
0 10
0 10
0 10
0 10
0 10
0 10

在上面的查詢的基礎上，我們作如下查詢：
select
tenth tenth,
min(normal) low_val,
max(normal) high_val,
max(normal) - min(normal) width,
round(100000 / (max(normal) - min(normal)),2) height
from (
select
normal,
ntile(10) over (order by normal) tenth
from t1
)
group by tenth
order by tenth
;

TENTH LOW_VAL HIGH_VAL WIDTH HEIGHT
---------- ---------- ---------- ---------- ----------
1 -32003 -8966 23037 4.34
2 -8966 -5883 3083 32.44
3 -5883 -3659 2224 44.96
4 -3659 -1761 1898 52.69
5 -1761 17 1778 56.24
6 17 1792 1775 56.34
7 1792 3678 1886 53.02
8 3678 5897 2219 45.07
9 5897 8974 3077 32.5
10 8974 34660 25686 3.89

這個輸出的含義是（我們僅解釋第一行，其他行的含義是一樣的），如果把表t1按照從小到大排列，並且按照100000一組把表t1分成10組，那麼第一組的最小的值是-32003，最大值是-8966，最小值和最大值差距是23037，在第一組裡，平均每一個值在表t1裡出現4.34次。透過這樣的一組資料，我們就可以比較清楚的看到t1的資料分佈情況，和我們上面的圖裡描述是差不多的。當然，如果我們分更過的組，反映的情況會更精確。

我們這索引做這些查詢，是因為histograms所表達的資訊是和我們上面的查詢所表達的資訊非常相似的。

現在我們對錶T1統計histograms資訊，同樣我們也是把t1的資料分為10組（按照oracle的說法，10個bucket）：
begin
dbms_stats.gather_table_stats(
user,
't1',
cascade => true,
estimate_percent => null,
method_opt => 'for columns normal size 10'
);
end;
/

查詢user_tab_histograms的資訊我們看到：
select ENDPOINT_NUMBER, ENDPOINT_VALUE from user_tab_histograms where TABLE_NAME='T1';

ENDPOINT_NUMBER ENDPOINT_VALUE
--------------- --------------
0 -32003
1 -8966
2 -5883
3 -3659
4 -1761
5 17
6 1792
7 3678
8 5897
9 8974
10 34660

這裡的意思就是把t1的資料平均的分成10組（也是按大小排序，也是每組十萬行），對於每一組在ENDPOINT_VALUE列上列出了臨界值。這和我們前面對資料的分組其實是完全一樣的，而且當我們對user_tab_histograms做如下查詢之後，我們得到了和前面一樣的結果：

SELECT ROWNUM TENTH,
PREV LOW_VAL,
CURR HIGH_VAL,
CURR - PREV WIDTH,
ROUND(100000 / (CURR - PREV),2) HEIGHT
FROM (SELECT ENDPOINT_VALUE CURR,
LAG(ENDPOINT_VALUE,1) OVER(ORDER BY ENDPOINT_NUMBER) PREV
FROM USER_TAB_HISTOGRAMS
WHERE TABLE_NAME = 'T1'
AND COLUMN_NAME = 'NORMAL')
WHERE PREV IS NOT NULL
ORDER BY CURR;

TENTH LOW_VAL HIGH_VAL WIDTH HEIGHT
---------- ---------- ---------- ---------- ----------
1 -32003 -8966 23037 4.34
2 -8966 -5883 3083 32.44
3 -5883 -3659 2224 44.96
4 -3659 -1761 1898 52.69
5 -1761 17 1778 56.24
6 17 1792 1775 56.34
7 1792 3678 1886 53.02
8 3678 5897 2219 45.07
9 5897 8974 3077 32.5
10 8974 34660 25686 3.89

這裡絕對不是巧合，而是因為我們手工作的實驗和Oracle收集histograms的方法是一致的，其實如果我們對dbms_stats.gather_table_stats的過程做sql_trace的話，我們會在trace檔案中看到如下SQL：
select
min(minbkt),
maxbkt,
substrb(dump(min(val),16,0,32),1,120) minval,
substrb(dump(max(val),16,0,32),1,120) maxval,
sum(rep) sumrep,
sum(repsq) sumrepsq,
max(rep) maxrep,
count(*) bktndv,
sum(case when rep=1 then 1 else 0 end) unqrep
from
(
select
val,
min(bkt) minbkt,
max(bkt) maxbkt,
count(val) rep,
count(val) * count(val) repsq
from
(
select /*+
cursor_sharing_exact dynamic_sampling(0) no_monitoring
*/
"NORMAL" val,
ntile(10) over(order by "NORMAL") bkt
from
"TEST_USER"."T1" t
where
"NORMAL" is not null
)
group by val
)
group by
maxbkt
order by
maxbkt
;

從這裡我們可以知道，histograms的作用就是用來描述你的表裡的資料集的狀態的，用來描述某個資料（或某個範圍的資料）的出現比率的。

2）關於histograms的常規討論：

histograms主要是在表的資料分佈是不均勻分佈的時候，用來幫助CBO計算selectivity和cardinality。
我們可以發現，Oracle會以兩種方式來表達histograms的資訊。
一種是在表裡的distinct key比較少的時候（少於255個distinct key），Oracle會以一種叫做frequency histogram的方式來儲存histograms資訊，或者準確的說，這種方式應該叫做cumulative frequency histogram。
另一種就是當表裡的distinct key比較多的時候（多於等於255），Oracle會以一種叫做height balanced histogram的方式來儲存histograms資訊，我們已經在剛才的的討論中看到了這種表現方式。
這兩種histograms還有兩種更貼切的叫法：
Height-based histograms
Value-Based Histograms

我們知道在user_tab_columns裡有這樣兩個列：NUM_DISTINCT和DENSITY，當沒有統計histograms資訊的時候，我們會看到DENSITY總是1/NUM_DISTINCT，但當我們統計了histograms後，
DENSITY的值就會有所改變。我們還會在後面看到，有時候雖然我們統計了histograms資訊，但CBO會使用DENSITY來計算selectivity和cardinality。

3）histograms和繫結變數

我們知道當我們擁有了histograms的統計資訊之後我們就可以使用這些資訊計算我們的selectivity和cardinality。但是如果我們使用了繫結變數的時候，情況總會有所改變。
首先，在Oracle9i裡面新引入了bind variable peeking的功能，這個功能我們前面講過，是一個帶繫結變數的SQL第一次parse的時候，讓CBO可以根據繫結的具體的值來決定所要使用的執行計劃，而以後如果遇到同樣的SQL，即使繫結變數的值不一樣，也不會在peek繫結變數的值，而是使用已經生成的計劃。這裡的一個潛在的問題就是如果我們有了histograms資訊，而且我們的資料分佈是一小部分資料的分佈和其他部分的分佈相差很遠，那麼當我們在做bind variable peeking，如果很不幸運的peek到了那一小部分的資料，就會導致以後所有的同樣的SQL都使用了不恰當的執行計劃。

當然這個bind variable peeking有時候也有意外，那就是如果我們存在shared pool裡的執行計劃資訊或其他相關的資訊由於某種原因失效了或者被age out of shared pool，那當我們再次執行這個SQL的時候，就會重新peek繫結變數的值，從而重新生成計劃。關於執行計劃資訊或其他相關的資訊的失效或age out，可以透過v$sql的reloads和invalidations欄位獲得。

和繫結變數有關的另一個就是引數cursor_sharing。
cursor_sharing這個引數有三個取值：

FORCE
Forces statements that may differ in some literals, but are otherwise identical, to share a cursor, unless the literals affect the meaning of the statement.

SIMILAR
Causes statements that may differ in some literals, but are otherwise identical, to share a cursor, unless the literals affect either the meaning of the statement or the degree to which the plan is optimized.

EXACT
Only allows statements with identical text to share the same cursor.

有時候，很可能是在OLTP的系統中，為了最大限度的減少SQL PARSE的消耗，讓類似的SQL可以儘可能的重用，我們會考慮設定把cursor_sharing設定為force。當cursor_sharing被設定為force的時候，最佳化器會用系統指定的繫結變數來替代SQL裡面所有的literal constants，然後以此為基礎判斷我們的shared pool裡面是不是有可以重用的cursor。按照我們上面的討論，設定cursor_sharing為force對histograms影響最大的。

這個問題可以有兩個work around,一是在我們認為影響會很到的SQL裡面加上hint /*+ cursor_sharing_exact */，這回告訴CBO對於這個SQL採用cursor_sharing=exact的策略。
另一個解決方法是設定cursor_sharing=similar，按照上面Oracle文件的說法，設定cursor_sharing為similar也會首先把SQL裡的literals替換為繫結變數，並且也會在第一次分析SQL的時候做bind variable peeking，但是當以後重新執行類似的SQL的時候，CBO會檢視如果發現新的繫結變數會影響到執行計劃（當然，之所以會產生不同的執行計劃往往是因為存在histograms），就會重新生成執行計劃。經過一些實驗，我們可以發現，當設定cursor_sharing=similar的時候，如果我們的條件是range scan或等於的條件，並且條件涉及的列上有histograms資訊的時候，CBO會在分析SQL的時候對繫結變數做檢查，如果發現新的繫結變數有可能影響SQL的執行計劃，則會重新評估並生成新的計劃。

但是往往我們在最佳化系統的一個方面的時候會導致其他方面的問題，cursor_sharing=similar就是一個很典型的例子，當我們這樣的設定的時候，首先最佳化器的壓力會變大，因為CBO要做很多的重新最佳化。
更嚴重的問題在於cursor_sharing=similar會導致同樣的SQL（除了繫結變數的值不一樣之外）在library cache裡面擁有很多不同的執行計劃，因為我們知道一個SQL下面的所有執行計劃都是被一個latch保護的，所以cursor_sharing=similar會導致更嚴重的latch 爭用。

因此當我們使用cursor_sharing=similar的時候，除非必要，無需統計histograms資訊，因為我們要保證我們為了解決一個問題不會導致其他的更嚴重的問題。

最後，當你設定CURSOR_SHARING為similar和force的時候，使用OUTLINES和EXPLAIN PLAN會遇到一些問題。
當你使用explain plan for {sql statement}, 或者create outline for {sql statement} 這樣的命令時，Oracle不會把裡面的常量替換為繫結變數，而是使用常量來生成執行計劃。對於explain plan來說，我們看到的計劃很可能不是真正執行時使用的計劃，而outline則更糟，因為即使你執行了相同的SQL，Oracle會在處理SQL之前把裡面的常量先替換為繫結變數，這樣你執行的SQL和outline裡面的SQL是不匹配的，從而導致outline不會被使用。

4）什麼時候Oracle無法使用histograms？

1. Oracle不能保證在join中可以充分使用histograms，如果你有一個列colx，Oracle只有你明確的指定了colx operation(,=,in,between等等) 常量（這個常量當然也可以是透過bind variable peeking獲得的）的時候，才會使用histograms，所以如果你執行了這樣的SQL：

select
t1.v1, t2.v1
from
t1,
t2
where
t1.n2 = 99
and t1.n1 = t2.n1
;

如果我們在t1和t2上都有histograms，Oracle會在t1.n2=99這個條件上使用histograms，但Oracle不能在and t1.n1 = t2.n1這個條件上使用histograms，當然如果我們的條件改成：
t1.n2 = 99
and t1.n2 = t2.n1
這時候histograms就可以使用了，因為Oracle會自己把這個SQL改寫成：
t1.n2 = 99
and
t2.n1 = 99

2. Oracle在分散式查詢中不會使用遠端表的histograms資訊。
我們可以做一個簡單的實驗看到這個結果：

首先建立一個指向自己的database link：
SQL> create database link loopback connect to lii identified by lii using 'REPDB1.CHP.HP.COM';

Database link created.

然後我們建立表t1：
create table t1 (
skew, skew2, padding
)
as
select r1, r2, rpad('x',200)
from
(
select /*+ no_merge */
rownum r1
from all_objects
where rownum <= 80
) v1,
(
select /*+ no_merge */
rownum r2
from all_objects
where rownum <= 80
) v2
where r2 <= r1
order by r2,r1
;

alter table t1 modify skew not null;
alter table t1 modify skew2 not null;
create index t1_skew on t1(skew);

然後我們收集統計資訊，包括histograms：

begin
dbms_stats.gather_table_stats(
user,
't1',
cascade => true,
estimate_percent => null,
method_opt => 'for all columns size 90'
);
end;
/

現在我們看一下查詢本地的t1和“遠端的”t1有什麼不同：
set autotrace traceonly explain

select
home.skew2,
away.skew2,
home.padding,
away.padding
from
t1 home, away
where
home.skew = 5
and away.skew = 5
and home.skew2 = away.skew2
;

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=33 Card=48 Bytes=16080)
1 0 HASH JOIN (Cost=33 Card=48 Bytes=16080)
2 1 REMOTE* (Cost=16 Card=41 Bytes=5248) LOOPBACK.CHP.HP.COM
3 1 TABLE ACCESS (FULL) OF 'T1' (Cost=16 Card=38 Bytes=7866)

2 SERIAL_FROM_REMOTE SELECT "SKEW","SKEW2","PADDING" FROM "T1" "AWAY" WHERE "SKEW"=5

select
home.skew2,
away.skew2,
home.padding,
away.padding
from
t1 home,
t1 away
where
home.skew = 5
and away.skew = 5
and home.skew2 = away.skew2
;

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=33 Card=22 Bytes=910
8)

1 0 HASH JOIN (Cost=33 Card=22 Bytes=9108)
2 1 TABLE ACCESS (FULL) OF 'T1' (Cost=16 Card=38 Bytes=7866)
3 1 TABLE ACCESS (FULL) OF 'T1' (Cost=16 Card=38 Bytes=7866)

儘管t1和其實是同一個表，但plan裡面顯示的cardinality是不一樣的，因為沒有考慮histograms，對於，很顯然cardinality是這樣算出來的：

SQL> select num_distinct from user_tab_columns where table_name='T1' and column_name='SKEW';

NUM_DISTINCT
------------
80

SQL> select count(*) from t1;

COUNT(*)
----------
3240

SQL> select ceil(3240/80) cardinality from dual;

CARDINALITY
-----------
41

5）Frequency Histograms （或Value-Based Histograms）

現在我們來研究一下histograms裡的frequency histograms。
類似與我們剛才測試分散式查詢時所建立的表t1，我們重建表t1，現在t1.skew這個列分佈情況是skew=1出現一次，skew=2出現兩次......skew=80出現80次。
建表指令碼如下：

create table t1 (
skew not null,
padding
)
as
with generator as (
select --+ materialize
rownum id
from all_objects
where rownum <= 5000
)
select
/*+ ordered use_nl(v2) */
v1.id,
rpad('x',400)
from
generator v1,
generator v2
where
v1.id <= 80
and v2.id <= 80
and v2.id <= v1.id
order by
v2.id,v1.id
;

我們來收集frequency histograms：
begin
dbms_stats.gather_table_stats(
user,
't1',
cascade => true,
estimate_percent => null,
method_opt => 'for all columns size 120'
);
end;
/

我們查詢user_table_histograms：
select
endpoint_number, endpoint_value
from
user_tab_histograms
where
column_name = 'SKEW'
and table_name = 'T1'
order by
endpoint_number
;

ENDPOINT_NUMBER ENDPOINT_VALUE
--------------- --------------
1 1
3 2
6 3
10 4
15 5
21 6
..................
..................
..................

ENDPOINT_NUMBER ENDPOINT_VALUE
--------------- --------------
3081 78
3160 79
3240 80

這個輸出和我們最開始看到的height balanced histograms所表達的意思是不同的，這個輸出對t1裡每一個distinct都保留了一行（所以才說frequency histograms是隻能用在distinct key <255的表上，因為histograms的最大bucket數是254）

從這個輸出裡面我們可以看到等於1的值有一個，等於1和2的值有3個（因此等於2的值有2個），等於1/2/3的值有6個。。。。。。從這個角度，我們常常把frequency histograms稱為累計的frequency histograms。

我們可以透過分析函式把累計的資訊轉化為每一個distinct key在表t1出現的次數：
select
endpoint_value row_value,
curr_num - nvl(prev_num,0) row_count
from (
select
endpoint_value,
endpoint_number curr_num,
lag(endpoint_number,1) over (
order by endpoint_number
) prev_num
from
user_tab_histograms
where
column_name = 'SKEW'
and table_name = 'T1'
)
order by
endpoint_value
;

ROW_VALUE ROW_COUNT
---------- ----------
1 1
2 2
3 3
4 4
5 5
6 6
7 7
8 8
.....................
.....................
.....................
ROW_VALUE ROW_COUNT
---------- ----------
78 78
79 79
80 80

如果你對於我們這個表1用dbms_stats來收集統計資訊的時候，你會發現如果你使用的bucket是80，你不會得到一個frequency histograms的統計資訊，而會得到一個height balanced histograms的統計資訊，我想這絕對是dbms_stats的一個問題，因為如果使用analyze命令的for all columns size 80的話，你是可以得到frequency histograms的統計資訊的。經過多次實驗你會發現對於表t1，如果你想得到frequency histograms，你的bucket至少得設定為107。由於histograms的最大bucket為254個，所以如果你的distinct key是大於180的話，用dbms_stats是永遠得不到frequency histograms的，這時候必須使用analyze。

現在我們對t1表進行一些測試，結果整理如下：

我們發現frequency histograms確實是夠精確，除了繫結變數，所有的常量查詢都得到了合理的cardinality，注意這裡CBO在我們認為是0行的地方統一的看作是1行，實際上除非你的條件裡面加入1=0這樣的條件，否則CBO一般是不允許cardinality為0的。

對於繫結變數的測試，我們看到還是在skew = :b1的時候使用了典型的：行數/distinct key，在skew between :b1 and :b2 使用了：5%*5%*行數。
其實我們透過explain plan看到的關於繫結變數的計劃通常是不準的，這一點我們可以用explain plan的計劃和sql_trace=true裡面的計劃做對比就可以了。下面做個小測試：

建表：
create table t1
as
select
object_id
from
dba_objects
where
rownum <= 1000;

alter table t1 add c1 number default 1;

update t1 set object_id=3 where object_id>2;
這張表object_id=2有1行，object_id=3有999行。

建索引：
create index t1_i1 on t1(object_id);

收集統計資訊：
begin
dbms_stats.gather_table_stats(
user,
't1',
cascade => true,
estimate_percent => null,
method_opt => 'for all columns size 10'
);
end;
/

我們看一下對於繫結變數b1=3的時候：

SQL> exec :b1 := 3;

PL/SQL procedure successfully completed.

SQL> select * from t1 where object_id= :b1;

999 rows selected.

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=2 Card=500 Bytes=3000)
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'T1' (Cost=2 Card=500 Bytes=3000)
2 1 INDEX (RANGE SCAN) OF 'T1_I1' (NON-UNIQUE) (Cost=1 Card=500)

對於不使用繫結變數的時候：
SQL> select * from t1 where object_id=3;

999 rows selected.

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=2 Card=999 Bytes=5994)
1 0 TABLE ACCESS (FULL) OF 'T1' (Cost=2 Card=999 Bytes=5994)

下面是我們的sql_trace裡面看到的關於使用繫結變數的結果，顯然還是走了全表掃描，和explain plan的輸出不一樣：
select *
from
t1 where object_id= :b1

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 41 0.00 0.00 0 44 0 999
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 43 0.00 0.00 0 44 0 999

Misses in library cache during parse: 1
Optimizer goal: ALL_ROWS
Parsing user id: 46

Rows Row Source Operation
------- ---------------------------------------------------
999 TABLE ACCESS FULL OBJ#(27781) (cr=44 r=0 w=0 time=899 us)

最後Jonathan提供了一個指令碼來收集多於254個distinct value的表上的frequency histograms，指令碼可以參考：c_skew_freq_02.sql (http://fusnow.itpub.net/resource/681/18332)

6）height balanced histogram（或Height-based histograms）

當我們給出的bucket數目不夠大的時候，Oracle會以height balanced的方式記錄histograms，也就是按照buckets的值把所有的資料平分，如果bucket是50，就把所有的資料平分為50等份，再告訴我們處於每個邊界的值。

我們用前面見過的一張表來看height balanced histograms具體是什麼樣的：

create table t1 (
skew not null,
padding
)
as
with generator as (
select --+ materialize
rownum id
from all_objects
where rownum <= 5000
)
select
/*+ ordered use_nl(v2) */
v1.id,
rpad('x',400)
from
generator v1,
generator v2
where
v1.id <= &m_demo_size
and v2.id <= &m_demo_size
and v2.id <= v1.id
order by
v2.id,v1.id
;

create index t1_i1 on t1(skew);

然後我們以bucket為75建立histograms資訊。
begin
dbms_stats.gather_table_stats(
user,
't1',
cascade => true,
estimate_percent => null,
method_opt => 'for all columns size 75'
);
end;
/

這張表的資料分佈就是1出現1次，2出現2次，3出現3次......最後80出現80次，如果我們把histograms的資訊查出來的話，類似於：
ENDPOINT_NUMBER ENDPOINT_VALUE
--------------- --------------
0 1
1 9
2 13
3 16
4 19
5 21
..................
..................
..................
62 73
64 74
65 75
67 76
69 77
71 78
73 79
75 80

59 rows selected.

我們發現查詢user_tab_histograms只有59行輸出，在仔細看看發現Oracle確實是產生了75個bucket，因為ENDPOINT_NUMBER的最大值是75，只不過在記錄統計的資訊Oracle進行了壓縮，省略了一些bucket的輸出，上面的輸出其實可以展開成如下形式：

表1：

ENDPOINT_NUMBER ENDPOINT_VALUE
60 72
61 73
62 73
63 74
64 74
65 75
66 76
67 76
68 77
69 77
70 78
71 78
72 79
73 79
74 80
75 80

在進一步解釋其他關於histograms的資訊之前，我們現看一看user_tab_columns裡的DENSITY這個列，

SQL> select density, NUM_DISTINCT, 1/NUM_DISTINCT from user_tab_columns where table_name='T1' and column_name='SKEW';

DENSITY NUM_DISTINCT 1/NUM_DISTINCT
---------- ------------ --------------
.013885925 80 .0125

當我們沒有histograms資訊的時候，DENSITY永遠等於1/NUM_DISTINCT，但是當我們統計了histograms之後，DENSITY就會發生改變，這個改變隨Oracle版本改變會有所不同：
8i gives a density of 0.006756757, creates 74 buckets, and reports 58 buckets.
9i gives a density of 0.013885925, creates 75 buckets, and reports 58 buckets.
10g gives a density of 0.013885925, creates 75 buckets, and reports 75 buckets.

讓我們再回頭關注我們的表t1的histograms資訊，我們尤其關注一下ENDPOINT_VALUE=75的情況，之所以關注75是因為我們發現75周圍的數值（比如74）都在輸出當中出現了多次，只有75只出現了一次，我們自己知道75其實出現的次數應該是不必74在表裡出現的次數少的，但不巧的是75在統計Histograms的時候時候出在了一個特殊的位置：

這種不巧會導致Oracle使用不同的機制來計算skew=75的cardinality，在oracle看來，當把histograms資料完全展開成上面表1的形式後，凡是在histograms資訊裡面出現2次或2次以上的資料都是常見的資料，叫做popular的資料，只出現一次或沒出現的資料都是un-popular的資料。

對於popular的資料，對於=的條件，計算cardinality的公式是：
cardinality=總行數*（出現次數/bucket總數）

比如我們上面的74的cardinality= 3240 * （2/75) = 86.4，和我們下面的實驗相符：

SQL> select count(*) from t1 where skew=74;

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=1 Card=1 Bytes=3)
1 0 SORT (AGGREGATE)
2 1 INDEX (RANGE SCAN) OF 'T1_I1' (NON-UNIQUE) (Cost=1 Card=86 Bytes=258)

對於un-popular的資料，對於=的條件，計算cardinality的公式是：
總行數*DENSITY，這裡就是3240*0.013885925=44.99：

SQL> select count(*) from t1 where skew=75;

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=1 Card=1 Bytes=3)
1 0 SORT (AGGREGATE)
2 1 INDEX (RANGE SCAN) OF 'T1_I1' (NON-UNIQUE) (Cost=1 Card=45 Bytes=135)

SQL> select count(*) from t1 where skew=2;

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=1 Card=1 Bytes=3)
1 0 SORT (AGGREGATE)
2 1 INDEX (RANGE SCAN) OF 'T1_I1' (NON-UNIQUE) (Cost=1 Card=45 Bytes=135)

range scan的公式是：
如果不跨整個的bucket:
Selectivity = (required range) / (high value - low value) + 2 * density
cardinality=每個bucket的行數*Selectivity

如果跨整個的bucket：
Selectivity = (required range) / (high value - low value) + 2 * density + 整個的bucket的個數
cardinality=每個bucket的行數*Selectivity

最後我們用一個更通用的例子（distinct key更多）來看一下有histograms的時候是如何計算range scan的cardinality的：

create table t1
as
/*
with generator as (
select --+ materialize
rownum n1
from all_objects
where rownum <= 5000
)
*/
select
/*+ ordered use_nl(v2) */
3000 + trunc(2000 * dbms_random.normal) n1,
lpad(rownum,10) small_vc,
rpad('x',100) padding
from
generator v1,
generator v2
where
rownum <= 10000
;

insert into t1
select
500 * (1 + trunc((rownum-1)/500)),
lpad(rownum,10),
rpad('x',100)
from
t1
;

commit;

begin
dbms_stats.gather_table_stats(
user,
't1',
cascade => true,
estimate_percent => null,
method_opt => 'for all columns size 250'
);
end;
/

histograms的資訊大致如下：
.......
ENDPOINT_NUMBER ENDPOINT_VALUE
--------------- --------------
8 -120
9 17
10 117
11 251
12 357
13 450
19 500
20 520
21 598

首先如果不跨整個的bucket:
select
small_vc
from t1
where n1 between 100 and 200
;
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=53 Card=63 Bytes=945)
1 0 TABLE ACCESS (FULL) OF 'T1' (Cost=53 Card=63 Bytes=945)

按照公式計算：
Selectivity = (required range) / (high value - low value) + 2 * density =
(200–117)/(251-117) + (117-100)/(117-17) + 2 * 0.000177746 =
0.619403 + 0.17 + .000355486 =
0.789047508

Cardinality = selectivity * number of rows IN A BUCKET =
0.789047508 * 80 = 63.1238

如果跨多個bucket：
select
small_vc
from t1
where n1 between 400 and 600
;
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=53 Card=685 Bytes=10275)
1 0 TABLE ACCESS (FULL) OF 'T1' (Cost=53 Card=685 Bytes=10275)

按照公式計算：
Selectivity = (required range) / (high value - low value) + 2 * density + 整個的bucket的個數
(450 - 400) / (450 - 357) + (600 - 598) / (670 - 598) + 2 * 0.000177746 + 8 =
50 / 93 + 2 / 72 + 0.000355486 +8 =
0.537634 + 0.0277778 + 0.000355486 + 8=
8.565768

Cardinality = selectivity * number of rows IN A BUCKET =
8.565867 * 80 = 685.3