上篇中，我們介紹了Multi-Column/Column Group的使用。本篇中我們繼續研究其中的本質問題。

4、Multi-Column本質論

在Oracle中，是怎麼組織多列組的資訊呢？我們首先從列統計量入手分析。

SQL> select column_name, num_distinct,SAMPLE_SIZE, AVG_COL_LEN, HISTOGRAM from user_tab_col_statistics where table_name='T';

COLUMN_NAM NUM_DISTINCT SAMPLE_SIZE AVG_COL_LEN HISTOGRAM

---------- ------------ ----------- ----------- ---------------

ID 3 5 3 FREQUENCY

NAME 4 5 3 FREQUENCY

SYS_STUIA0 4 5 12 FREQUENCY

V924QODN5R

5SCAKM60G#

在列中存在一個特殊列的統計資訊，這裡的列名同我們生成擴充統計量時候的那個字串。

SQL> var vc_res varchar2(100);

SQL> exec :vc_res := dbms_stats.create_extended_stats('SCOTT','T','(id,name)');

PL/SQL procedure successfully completed

vc_res

---------

SYS_STUIA0V924QODN5R5SCAKM60G#

同時，Oracle也提供了一個檢視user_stat_extensions來檢視生成的擴充統計量。

SQL> select * from user_stat_extensions where extension_name = 'SYS_STUIA0V924QODN5R5SCAKM60G#';

TABLE_NAME EXTENSION_NAME EXTENSION CREATOR DROPPABLE

---------- ------------------------------ -------------------- ------- ---------

T SYS_STUIA0V924QODN5R5SCAKM60G# ("ID","NAME") USER YES

那麼，Oracle是不是同函式索引採用相同的內部策略，構建一個虛擬列進行管理呢？我們只有去到col$基表中進行檢查。

SQL> select object_id from dba_objects where object_name='T' and wner='SCOTT';

OBJECT_ID

----------

75482

SQL> select col#, name,DEFAULT$ from col$ where obj#=75482;

COL# NAME DEFAULT$

---------- ---------- ------------------------------

1 ID

2 NAME

0 SYS_STUIA0 SYS_OP_COMBINED_HASH("ID","NAM

V924QODN5R E")

5SCAKM60G#

果然，此處顯示的內容是：Oracle使用類似虛擬列的方法，構建了一個列。之後對這個列進行統計量收集。

5、Column Group的失效場景

在筆者的實驗中，發現並不是建立了column group之後，所有的統計量估算都是正確的。起碼當條件中存在非等號之後，擴充統計量估值是可能錯誤的。

SQL> exec dbms_stats.gather_table_stats(user,'EMP',cascade => true);

PL/SQL procedure successfully completed

--結果集合為3

SQL> select * from emp where job='MANAGER' and sal>2000;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

----- ---------- --------- ----- ----------- --------- --------- ------

7566 JONES MANAGER 7839 1981-4-2 2975.00 20

7698 BLAKE MANAGER 7839 1981-5-1 2850.00 30

7782 CLARK MANAGER 7839 1981-6-9 2450.00 10

檢視執行計劃中的估算值。

SQL> explain plan for select * from emp where job='MANAGER' and sal>2000;

Explained

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------------

Plan hash value: 3956160932

--------------------------------------------------------------------------

--------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 1 | 38 | 3 (0)| 00:00:01 |

|* 1 | TABLE ACCESS FULL| EMP | 1 | 38 | 3 (0)| 00:00:01 |

--------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

1 - filter("JOB"='MANAGER' AND "SAL">2000)

13 rows selected

此時，我們收集一下擴充統計量。

SQL> var vc_res varchar2(1000);

SQL> exec :vc_res := dbms_stats.create_extended_stats('SCOTT','EMP','(job,sal)');

PL/SQL procedure successfully completed

vc_res

---------

SYS_STU73TUM4UV1A$7U9OVY05$MH6

SQL> select * from user_stat_extensions;

TABLE_NAME EXTENSION_NAME EXTENSION CREATOR DROPPABLE

---------- ------------------------------ -------------------- ------- ---------

EMP SYS_STU73TUM4UV1A$7U9OVY05$MH6 ("JOB","SAL") USER YES

SQL> exec dbms_stats.gather_table_stats(user,'EMP',method_opt => 'for columns (job,sal) size skewonly');

PL/SQL procedure successfully completed

SQL> select column_name, num_distinct,SAMPLE_SIZE, AVG_COL_LEN, HISTOGRAM from user_tab_col_statistics where table_name='EMP';

COLUMN_NAM NUM_DISTINCT SAMPLE_SIZE AVG_COL_LEN HISTOGRAM

---------- ------------ ----------- ----------- ---------------

（篇幅原因，有省略。。。。。。）

DEPTNO 3 14 3 FREQUENCY

SYS_STU73T 12 14 12 FREQUENCY

UM4UV1A$7U

9OVY05$MH6

此時，執行計劃並沒有改變。

SQL> explain plan for select * from emp where job='MANAGER' and sal>2000;

Explained

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------------

Plan hash value: 3956160932

--------------------------------------------------------------------------

--------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 1 | 38 | 3 (0)| 00:00:01 |

|* 1 | TABLE ACCESS FULL| EMP | 1 | 38 | 3 (0)| 00:00:01 |

--------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

1 - filter("JOB"='MANAGER' AND "SAL">2000)

13 rows selected

這說明擴充統計量在非等號情況下，是存在一些問題的。

6、其他注意問題和結論

此外，在使用column group上，我們還需要注意下面的問題：

ü 擴充列中不允許出現虛擬列（Virtual Column）；

ü 不能對sys schema下的資料表列建立column group；

ü 不能對聚簇表（Cluster Table）、索引組織表（Index Organized Table）、臨時表（Temporary Table）和外部表（External Table）上的列建立Column Group；

ü 一個資料表中建立的擴充列數目不能超過20和資料表10%非虛擬列的數目；

ü 一個擴充列組中包括了列數目位於2-32的範圍內；

ü 一個列只能出現在一個擴充列組中；

ü 列組中不能包括表示式；

ü compatible引數必須在11.0.0.0以上；

最後，個人感覺在實際中，特別是開發環境下很少會使用到column group的功能。因為解決執行計劃問題的手段很多，column group不是最優的方法。而在運維環境中，常常會遇到書寫很糟糕的SQL和設計。此時運維人員通常沒有機會修改SQL原始碼。所以，column group作為一種運維手段，是可以進行嘗試的。

Oracle 11g的多列統計（Multi Column）值（下）

相關文章