Oracle擴充的統計資訊

我們在收集列的統計資訊與長條圖時，往往都是對某一列的收集。當謂詞使用多個相關列時，會導致約束條件的冗餘。這幾個相關的列也被稱作關聯列。出現這種情況時，查詢最佳化工具也會做出不準確的判斷。所以我們必須對這些相關列收集統計資訊或長條圖來描述這種依賴關係。

幸運的是，從Oracle11g開始，資料庫可以收集基於運算式或者一組列上的對象統計資訊和長條圖，從而解決這種問題。這種新的統計叫做擴充的統計資訊（extension statistics）。

這種技術實際上是基於運算式或一組列建立一個隱藏列，叫做擴充（extension），再在擴充列上收集統計資訊與長條圖。

一、如何定義擴充列

可以調用Oracle內建的包dbms_stats的函數create_extended_stats來實現。下面對測試表的相關列做擴充列。測試表語句參見《Oracle中收集表與列統計資訊》（）一個基於運算式upper(pad)，另一個基於val2和val3組成的列組。在測試表裡，val2和val3取值相同，高度關聯。

SELECT DBMS_STATS.CREATE_EXTENDED_STATS(OWNNAME  => 'TEST',
                                        TABNAME  => 'T',
                                        EXTENSION => '(upper(pad))'),
      DBMS_STATS.CREATE_EXTENDED_STATS(OWNNAME  => 'TEST',
                                        TABNAME  => 'T',
                                        EXTENSION => '(val2,val3)')
  FROM DUAL;

這樣就定義了兩個擴充列。他們分別是基於運算式的和基於多列的。

二、如何查詢擴充列資訊

基於user_stat_extensions、dba_stat_extensions和all_stat_extensions，都能查詢相關的擴充列資訊。

SELECT COLUMN_NAME, DATA_TYPE, HIDDEN_COLUMN, DATA_DEFAULT
  FROM USER_TAB_COLS
 WHERE TABLE_NAME = 'T';

COLUMN_NAME                              DATA_TYPE  HID DATA_DEFAULT
---------------------------------------- ---------- --- ----------------------------------------
ID                                      NUMBER    NO
VAL1                                    NUMBER    NO
VAL2                                    NUMBER    NO
VAL3                                    NUMBER    NO
PAD                                      VARCHAR2  NO
SYS_STU0KSQX64#I01CKJ5FPGFK3W9          VARCHAR2  YES UPPER("PAD")
SYS_STUPS77EFBJCOTDFMHM8CHP7Q1          NUMBER    YES SYS_OP_COMBINED_HASH("VAL2","VAL3")

從data_default這列我們可以觀察到，SYS_OP_COMBINED_HASH("VAL2","VAL3")，擴充列統計使用了雜湊函數，所以val2和val3隻有使用相等（=）謂詞時，最佳化器才使用擴充統計資訊。

二、如何刪除擴充統計資訊

依然使用Oracle內建的dbms_stats提供的過程drop_extended_stats來刪除擴充統計資訊。

BEGIN
  DBMS_STATS.DROP_EXTENDED_STATS(OWNNAME  => 'TEST',
                                TABNAME  => 'T',
                                EXTENSION => '(upper(pad))');
  DBMS_STATS.DROP_EXTENDED_STATS(OWNNAME  => 'TEST',
                                TABNAME  => 'T',
                                EXTENSION => '(val2,val3)');
END;

最後提一下，擴充統計資訊是基於Oracle11g的另一個新特性——虛擬列。它並不儲存資料，那它有什麼現實意義呢？我們可以設想，在開發代碼中，有很多sql語句用到了upper(varchar2)、trunc(date)，此時儘管在這些列上建立索引，執行計畫依然不會走索引，為了避免全表掃描，我們最好的方法是改寫文法，謂詞盡量不被函數轉換，但有時候在不好轉換語句時，可以建立一個虛擬列，然後在虛擬列上建立索引。比如下面的方法：

CREATE TABLE persons(
NAME VARCHAR2(100),
name_upper AS (UPPER(NAME)));

如果在頻繁查詢使用了upper(name)=’MIKE’,就可以使用name_upper=’MIKE’,前提是虛擬列建立索引。當然虛擬列也不不好的地方，比如插入資料不能指定所有列，因為虛擬列是不存資料的。