數(shù)據(jù)庫(kù)的版本是11.2.0.3：

創(chuàng)建一個(gè)測(cè)試表T1：

SQL> create table t1 as select *

from dba_users;

Table created

從如下查詢結(jié)果中我們可以看到,，表T1的OBJECT_ID是104192：

SQL> select object_id from dba_objects

 where owner=’SCOTT’ 

and object_name=’T1′;

 OBJECT_ID

——————-

  104192

表T1的列user_id所對(duì)應(yīng)的INTCOL#是2：

SQL> select name,intcol# from sys.col$ 

where obj#=104192 

and name=’USER_ID’;

NAME                       INTCOL#

——————                ———-

USER_ID                           2

從如下結(jié)果里可以看到，SYS.COL_USAGE$現(xiàn)在還沒有列USER_ID的使用記錄：

SQL> select obj#,intcol#,

equality_preds 

from sys.col_usage$ 

where obj#=104192;

      OBJ#    INTCOL# EQUALITY_PREDS

我們現(xiàn)在來使用一下列USER_ID：

SQL> select count(*) from t1

where user_id=5;

  COUNT(*)

—————–

   1

使用完后,，我們發(fā)現(xiàn)SYS.COL_USAGE$還是沒有列USER_ID的使用記錄：

SQL> select obj#,intcol#,

equality_preds

from sys.col_usage$ 

where obj#=104192;

      OBJ#    INTCOL# EQUALITY_PREDS

———- ———- ————–

這個(gè)是正常的,，這里不是沒有列USER_ID的使用記錄，是已經(jīng)有了但只是還沒有被持久化到SYS.COL_USAGE$中,，這里需要我們手工執(zhí)行一下dbms_stats.gather_table_stats,，這樣就能將USER_ID的使用記錄flush到SYS.COL_USAGE$中了，然后我們就能看到了：

但現(xiàn)在的問題是無論我們?cè)趺磮?zhí)行dbms_stats.gather_table_stats,，列user_id上的直方圖統(tǒng)計(jì)信息就是沒有（這也是那位朋友問的問題）：

這里除非我們手工指定user_id列所用的bucket的數(shù)量：

手工指定了直方圖統(tǒng)計(jì)信息的bucket的數(shù)量為39后,，明明列user_id的distinct值的數(shù)量也是39，為什么這里直方圖的類型居然是HEIGHT BALANCED,？按道理講應(yīng)該是FREQUENCY?。?/span>

當(dāng)看到上述測(cè)試結(jié)果的時(shí)候,，我意識(shí)到一定是什么地方出了問題,。

因?yàn)樯鲜霈F(xiàn)象的出現(xiàn)已經(jīng)顛覆了我之前對(duì)直方圖統(tǒng)計(jì)信息的如下兩個(gè)認(rèn)識(shí)：

我們來復(fù)習(xí)一下Oracle關(guān)于自動(dòng)收集直方圖統(tǒng)計(jì)信息的定義：

Oracle在“SIZE Clause in METHOD_OPT Parameter of DBMS_STATS Package (Doc ID 338926.1)”中明確指出，METHOD_OPT的值中的AUTO的含義為如下所示：

AUTO: Oracle determines the columns to collect histograms based on data distribution and the workload of the columns.

這里的“workload of the columns”指的應(yīng)該就是目標(biāo)列是否在SYS.COL_USAGE$中有使用記錄,。

注意到Oracle這里還提到了另外一個(gè)條件——“based on data distribution”（這也是我之前沒有注意到的條件）,，但這里的具體含義是什么？

“based on data distribution”直譯過來就是目標(biāo)列數(shù)據(jù)的分布,。說白了就是目標(biāo)列的數(shù)據(jù)分布確實(shí)得是傾斜的,，只有滿足這個(gè)前提條件，再加上該目標(biāo)列在SYS.COL_USAGE$中有使用記錄,，Oracle在自動(dòng)收集直方圖統(tǒng)計(jì)信息的時(shí)候才會(huì)對(duì)該列收集直方圖統(tǒng)計(jì)信息,。

搞清楚了上述知識(shí)點(diǎn),，那位朋友問的問題自然就有答案了——對(duì)于表T1的列user_id而言，其distinct值的數(shù)量和表T1的數(shù)據(jù)量相同,，所以這里即使user_id在SYS.COL_USAGE$中有使用記錄,，Oracle在自動(dòng)收集直方圖統(tǒng)計(jì)信息的時(shí)候也不會(huì)對(duì)user_id收集直方圖統(tǒng)計(jì)信息：

現(xiàn)在我們來驗(yàn)證一下上述理論，往表T1中插入一條記錄,，使得user_id的distinct值的數(shù)量小于表T1的數(shù)據(jù)量,，這樣當(dāng)我們?cè)俅螌?duì)表T1收集統(tǒng)計(jì)信息的時(shí)候，user_id列的直方圖統(tǒng)計(jì)信息應(yīng)該就有了,。

先把之前對(duì)user_id列手工指定bucket數(shù)量收集的直方圖統(tǒng)計(jì)信息刪掉：

對(duì)表T1插入一條user_id列的值和現(xiàn)有值重復(fù)的記錄：

SQL> insert into t1 select * 

from t1 where user_id=5;

1 row inserted

SQL> commit;

Commit complete

現(xiàn)在user_id列的distinct值的數(shù)量已經(jīng)小于表T1的數(shù)據(jù)量了：

此時(shí)對(duì)表T1再次收集統(tǒng)計(jì)信息：

SQL> exec dbms_stats.

gather_table_stats

(ownname=>’SCOTT’,

tabname=>’T1′,estimate_percent=>100);

PL/SQL procedure successfully completed

從如下查詢結(jié)果里我們可以看到，現(xiàn)在user_id列上終于有了直方圖統(tǒng)計(jì)信息,，且其類型就是FREQUENCY,，這就和我們以前的認(rèn)知匹配上了，同時(shí)也驗(yàn)證了我們剛才的分析結(jié)論：

再次刪除user_id列上的直方圖統(tǒng)計(jì)信息：

我們?cè)俅我允止ぶ付╞ucket數(shù)量的方式收集user_id列上的直方圖統(tǒng)計(jì)信息：

SQL>  exec dbms_stats.gather_table_stats

(ownname=>’SCOTT’,tabname=>’T1′,

method_opt=>’for columns size 39 USER_ID‘,estimate_percent=>100);

PL/SQL procedure successfully completed

從如下查詢結(jié)果我們可以看到,，現(xiàn)在user_id列上的直方圖統(tǒng)計(jì)信息的類型已經(jīng)不是之前的HEIGHT BALANCED了,，而是變成了FREQUENCY：

這說明我們之前的認(rèn)識(shí)（在手工收集直方圖統(tǒng)計(jì)信息的時(shí)候，如果我手工指定的bucket的數(shù)量等于目標(biāo)列的distinct值的數(shù)量，且這個(gè)值是小于等于254的話,，那么Oracle此時(shí)收集的直方圖統(tǒng)計(jì)信息的類型應(yīng)該是FREQUENCY）成立的前提條件是該列的數(shù)據(jù)分布是傾斜的,。

通過這篇文章，我們介紹了如下兩個(gè)關(guān)于直方圖統(tǒng)計(jì)信息的有趣知識(shí)點(diǎn)：

1,、如果目標(biāo)列的distinct值的數(shù)量和目標(biāo)表的數(shù)據(jù)量相同,，即使該目標(biāo)列在SYS.COL_USAGE$中有使用記錄，Oracle在自動(dòng)收集直方圖統(tǒng)計(jì)信息的時(shí)候也不會(huì)對(duì)該列收集直方圖統(tǒng)計(jì)信息,；

2,、在手工收集直方圖統(tǒng)計(jì)信息的時(shí)候，如果我手工指定的bucket的數(shù)量等于目標(biāo)列的distinct值的數(shù)量,，且這個(gè)值是小于等于254的話,，那么Oracle此時(shí)收集的直方圖統(tǒng)計(jì)信息的類型應(yīng)該是FREQUENCY——這個(gè)結(jié)論成立的前提條件是該列的數(shù)據(jù)分布是傾斜的。