DB2 程式設計技巧精選 (六) - SQL 優化、SP 選擇與目錄查詢

2. 程式設計效能進階

2.2 善用複雜 SQL 減少語句數量

• DB2 SQL Engine 對複雜查詢優化能力強，將多條簡單 SQL 合併為單一複雜 SQL 可減少 RESET SQLCODE/STATE 等內部操作開銷。
• 策略：減少 SQL 陳述式總數，讓優化器有更完整的上下文進行存取路徑選擇。
• 對比：Oracle 傾向拆解複雜 SQL，因其優化器在處理龐大查詢時效果較差。

2.3 SQL SP vs C SP 選擇指南

決策原則：以「工作量最小化」為前提，效能差異可接受時優先用 SQL SP。

2.4 查詢優化參數：HASH JOIN 與 RR_TO_RS

-- 啟用 Hash Join (需實例重啟生效)
db2set DB2_HASH_JOIN=Y
db2stop && db2start

-- 將 RR 隔離等級自動降為 RS，減少 Next-Key Lock 管理開銷
db2set DB2_RR_TO_RS=Y
db2stop && db2start

• Hash Join：大表連接無索引時效能提升顯著 (實測 7 張千萬列表 Join 從 72 秒大幅降低)。
• RR_TO_RS：應用程式若不強制需 RR，開啟此參數可減少鎖管理負擔。

2.5 避免 COUNT(*) 與 EXISTS 全表掃描

1. COUNT(*)：必須全表掃描，極慢，嚴禁用於大表判斷是否有資料。
2. EXISTS：遇第一筆符合即停止，較快，但仍可能掃描大量頁面。
3. 最佳替代方案：SELECT INTO + Exception Handler

   DECLARE CONTINUE HANDLER FOR NOT FOUND SET V_NOTFOUND = 1;
   DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET V_ERROR = 1;
   
   SELECT COL1 INTO V_VAR FROM BIG_TABLE WHERE KEY = ? FETCH FIRST 1 ROW ONLY;
   
   IF V_NOTFOUND = 1 THEN ... -- 無資料
   ELSEIF V_ERROR = 1 THEN ... -- 多筆資料 (SQLCODE -811)
   ELSE ... -- 找到單筆
   END IF;

   此法只讀取必要頁面，效能最佳。

4. 游標計數法：若需判斷「是否超過 N 筆」，可用游標迴圈計數，達標即跳出，避免全表掃描。

3. 目錄查詢實用 SQL

3.1 檢視儲存程序原始碼

SELECT TEXT FROM SYSCAT.PROCEDURES WHERE PROCNAME = 'PROC1';

3.2 檢視表結構

DESCRIBE TABLE SYSCAT.PROCEDURES;
-- 或
DESCRIBE SELECT * FROM SYSCAT.PROCEDURES;

3.3 反向查詢：表被哪些 SP 使用

SELECT PROCNAME
FROM SYSCAT.PROCEDURES
WHERE SPECIFICNAME IN (
  SELECT DNAME FROM SYSIBM.SYSDEPENDENCIES
  WHERE BNAME IN (
    SELECT PKGNAME FROM SYSCAT.PACKAGEDEP WHERE BNAME = 'TB_BRANCH'
  )
);

3.4 查詢 SP 使用了哪些表

SELECT BNAME
FROM SYSCAT.PACKAGEDEP
WHERE BTYPE = 'T' AND PKGNAME IN (
  SELECT BNAME FROM SYSIBM.SYSDEPENDENCIES
  WHERE DNAME IN (
    SELECT SPECIFICNAME FROM SYSCAT.PROCEDURES
    WHERE PROCNAME = 'PR_CLEAR_MATCH_DIVIDE_SHA'
  )
);

3.5 查詢 Function 被哪些 SP 使用

SELECT PROCNAME
FROM SYSCAT.PROCEDURES
WHERE SPECIFICNAME IN (
  SELECT DNAME FROM SYSIBM.SYSDEPENDENCIES
  WHERE BNAME IN (
    SELECT PKGNAME FROM SYSCAT.PACKAGEDEP
    WHERE BNAME IN (
      SELECT SPECIFICNAME FROM SYSCAT.FUNCTIONS
      WHERE FUNCNAME = 'GET_CURRENT_DATE'
    )
  )
);

3.6 一次 ALTER TABLE 新增多欄位

db2 "ALTER TABLE TB_TEST ADD COLUMN T1 CHAR(1) ADD COLUMN T2 CHAR(2) ADD COLUMN T3 INT"

• 單一陳述式完成多欄位新增，減少表鎖定時間與日誌量。

Function 相依性管理提醒

• 若要 DROP FUNCTION，必須先移除所有呼叫該 Function 的 SP。
• 建立 SP 時，被呼叫的 Function 必須已存在。

1. 上一篇文章： DB2 程式設計技巧精選 (五) - 儲存程序、效能與管理指令
2. 下一篇文章： DB2 程式設計技巧精選 (七) - 系統管理、安裝與環境設定

DB2 教學

MyBatis 延遲加載深度解析：原理、配置與調試實戰

MyBatis 延遲加載深度解析：原理、配置與調試實戰 業

DB2 Admin 管理員帳號刪除操作步驟圖解

背景情境 安裝 DB2 資料庫時需輸入一個使用者名稱，該帳號

Redis 發布/訂閱訊息佇列實戰指南

Redis 發布/訂閱訊息佇列實戰指南 Redis 內建的

Redis 存儲圖片方案對比：Base64/URL/路徑 vs 對象序列化深度解析

Redis 存儲圖片方案對比：Base64/URL/路徑 v