Oracle 11g 統計信息新特性：擴展統計與列組合直方圖

痛點：單列統計無法捕捉列相關性

優化器預設假設列之間獨立分布，導致多列條件（如 WHERE empno=... AND job=...）基數估算嚴重偏離，產生惡劣執行計劃。

解決方案：擴展統計

11g 引入 DBMS_STATS.CREATE_EXTENDED_STATS，可為**列組合**或**運算式**建立統計，讓優化器掌握真實相關性。

實戰步驟

1. 建立列組合虛擬欄位

SELECT DBMS_STATS.CREATE_EXTENDED_STATS('SCOTT', 'EMP', '(EMPNO, JOB)') FROM DUAL;
-- 回傳系統生成的虛擬欄位名，如 SYS_STU9D8L7V4NR#823O9R1EKCA6C

2. 查看生成的虛擬欄位與統計

SELECT * FROM DBA_TAB_COLS WHERE COLUMN_NAME LIKE 'SYS_STU%';
SELECT * FROM DBA_STAT_EXTENSIONS WHERE EXTENSION_NAME = 'SYS_STU9D8L7V4NR#823O9R1EKCA6C';

3. 為列組合收集直方圖

EXEC DBMS_STATS.GATHER_TABLE_STATS(
  'SCOTT', 'EMP',
  METHOD_OPT => 'FOR COLUMNS (EMPNO, JOB) SIZE AUTO');

效果驗證

EXPLAIN PLAN FOR SELECT * FROM SCOTT.EMP WHERE EMPNO=7788 AND JOB='ANALYST';
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
-- 觀察 Rows 估算是否接近實際返回行數

適用場景與限制

• ✅ 多列等值連接/過濾、列相關性強（如城市+郵遞區號、年+月）
• ✅ 函數運算式（如 UPPER(last_name)）
• ❌ 不支援外部表、叢集表、嵌套表
• ❌ 虛擬欄位不計入 USER_TAB_COLUMNS 實際欄位數，不影響 DESC 顯示

維護建議

1. 定期 DBMS_STATS.GATHER_TABLE_STATS(..., METHOD_OPT=>'FOR ALL COLUMNS SIZE AUTO FOR COLUMNS SIZE AUTO (col1,col2)') 同步更新。
2. 結合 DBMS_STATS.SEED_COL_USAGE / REPORT_COL_USAGE 發現高價值列組合。
3. 升級至 12c+ 後可利用 AUTO_STAT_EXTENSIONS 自動偵測建立。