一、利用約束提升查詢優化
定義 唯一性、檢查、參考完整性約束，不僅保障數據質量，更為優化器提供語義信息，啟用多種查詢重寫優化：
- 連接消除：若外鍵約束保證子表行必有父表匹配行，且查詢未選父表列，優化器自動刪除連接。
- 聚合下推 / FETCH FIRST N ROWS 優化。
- 去除冗余
DISTINCT。
信息約束 (Informational Constraints)
若應用層已保證完整性，可創建不強制執行、僅供優化器參考的約束：
ALTER TABLE DAILY_SALES
ADD CONSTRAINT FK_SALES_CUST_INFO
FOREIGN KEY (CUST_KEY) REFERENCES CUSTOMER (CUST_KEY)
NOT ENFORCED ENABLE QUERY OPTIMIZATION;
NOT ENFORCED：運行時不檢查，零開銷。ENABLE QUERY OPTIMIZATION：優化器可用於查詢重寫。- 適用：ETL 已驗證、數據倉庫、只讀報表表。
案例：連接消除
-- 原查詢：三表連接
SELECT AMT_SOLD, SALE_PRICE, PROD_DESC
FROM DAILY_SALES, PRODUCT, CUSTOMER
WHERE DAILY_SALES.PROD_KEY = PRODUCT.PRODKEY
AND DAILY_SALES.CUST_KEY = CUSTOMER.CUST_KEY;
-- 若 DAILY_SALES.CUST_KEY 有 FK 參照 CUSTOMER (ENFORCED 或 INFO)
-- 且查詢未選 CUSTOMER 任何列
-- 優化器自動重寫為：
SELECT AMT_SOLD, SALE_PRICE, PROD_DESC
FROM DAILY_SALES, PRODUCT
WHERE DAILY_SALES.PROD_KEY = PRODUCT.PRODKEY;
前提：應用必須保證數據完整性，否則結果可能錯誤 (如孤兒行存在)。
二、複雜查詢中使用 REOPT 綁定選項與輸入變量
輸入變量 (Parameter Markers) 的雙刃劍
| 場景 | 影響 |
|---|
| OLTP 簡單查詢、高頻重複執行 | 利好：復用動態語句緩存編譯計畫，避免重複編譯開銷 |
| 複雜分析查詢、數據分布傾斜、低頻執行 | 利空：優化器無實際值估算選擇率，生成通用但低效計畫 (如錯誤選擇全表掃描) |
REOPT 選項詳解
| 選項 | 行為 | 適用場景 | 開銷 |
|---|
| REOPT(NONE) (預設) | PREPARE 時編譯，使用預設選擇率估算 | OLTP 簡單高頻查詢 | 最低 |
| REOPT(ONCE) | 首次 OPEN/EXECUTE 時用首組實際值編譯，後續復用該計畫 | 首組值具代表性、後續值分布相似的複雜查詢 | 中 (首次編譯) |
| REOPT(ALWAYS) | 每次 OPEN/EXECUTE 都用實際值重新編譯 | 值分布差異極大、必須精確選擇率的關鍵複雜查詢 | 高 (每次編譯) |
綁定語法
# 綁定單一套件指定 REOPT
BIND myapp.bnd REOPT(ALWAYS)
# 重綁現有套件
REBIND PACKAGE myschema.mypkg REOPT(ONCE)
# 動態 SQL 隱式準備 (SET CURRENT REOPT)
SET CURRENT REOPT = ALWAYS; -- 當前會話生效
特殊寄存器場景
涉及 CURRENT DATE、CURRENT TIMESTAMP 的查詢：
WHERE TRANS_DATE = CURRENT DATE - 30 DAYS
優化器在 PREPARE 時不知實際日期值，REOPT(ALWAYS) 可延遲至 OPEN 時獲取實際日期值重編譯，生成精確計畫 (如分區剪枝)。
決策矩陣
查詢複雜度高 + 值分布傾斜/關鍵業務
│
├─ 首組值具代表性 → REOPT(ONCE) (平衡編譯開銷與計畫質量)
│
└─ 值分布差異極大 / 必須精確 → REOPT(ALWAYS) (接受編譯開銷換穩定性能)
三、綜合最佳實踐
- 約束先行：物理設計階段定義 PK、FK、CK；數據倉庫階段補充信息約束。
- 識別關鍵複雜查詢：長時間運行、大表掃描、多表連接、參數敏感。
- 測試 REOPT 效果：在測試環境對比
REOPT(NONE/ONCE/ALWAYS) 的編譯時間、執行時間、計畫差異。
- 會話級控制：應用代碼中關鍵查詢前執行
SET CURRENT REOPT = ALWAYS，查詢後恢復 NONE，避免全局影響。
- 監控編譯開銷：監控
STMT_EXEC_TIME vs STMT_COMPILE_TIME，防止過度編譯拖垮吞吐。
結語
約束是優化器的語義眼鏡，REOPT 是參數敏感查詢的手術刀。定義正確約束 (含信息約束) 讓優化器「看懂」數據關係，自動消除冗余連接；為關鍵複雜查詢選擇恰當 REOPT 模式，讓實際參數值引導優化器生成精準計畫。兩者結合，是 DB2 查詢優化從「靜態猜測」邁向「動態精準」的關鍵躍升。
- 上一篇文章： DB2 Restore 使用技巧：TSM 环境下的恢复实战
- 下一篇文章： DB2 64 位實例突破 4GB 記憶體限制：大內存配置、緩衝池擴展、AIX/HP-UX/Solaris/Linux/Windows 平台差異
Copyright ©
程式碼之家 All Rights Reserved
