Oracle 本地分區索引優化百萬級三表聯結：從 60 秒降至 2 秒實戰

案例背景

三張百萬級分區表（以 ACCTSTRINGDATE 為分區鍵）進行 INNER JOIN，原 SQL 執行 60+ 秒，目標 <5 秒。

原始 SQL 結構

SELECT /*+ PARALLEL(src, 8) */ DISTINCT
  src.systemname, src.databasename, src.tablename, src.username
FROM meta_dbql_table_usage_exp_hst src
INNER JOIN dr_qry_log_exp_hst rl
  ON src.acctstringdate = rl.acctstringdate
  AND src.queryid = rl.queryid
  AND src.systemname = rl.systemname
INNER JOIN meta_dr_qry_log_tgt_all_hst tgt
  ON UPPER(tgt.systemname) = UPPER('MOZART')
  AND UPPER(tgt.databasename) = UPPER('GDW_TABLES')
  AND UPPER(tgt.tablename) = UPPER('SSA_SLNG_LSTG_MTRC_SD')
  AND src.acctstringdate = tgt.acctstringdate
  AND rl.statement_id = tgt.statement_id
  AND rl.systemname = tgt.systemname
  AND tgt.acctstringdate > SYSDATE - 30
  AND NOT ( ...排除條件... )
  AND tgt.systemname IS NOT NULL ...

執行計畫診斷

計畫顯示兩層 NESTED LOOPS，最內層對 META_DBQL_TABLE_USAGE_EXP_HST 執行 全表掃描（Id 15），導致大量重複 I/O。

NESTED LOOP 高效前提

驅動資料集小，且被驅動表在連接欄位上有索引。

索引缺口分析

-- 現有索引
META_DR_QRY_LOG_TGT_ALL_IDX  ON (STATEMENT_ID, ACCTSTRINGDATE)
META_DBQL_TUSAGE_EHST_IDX    ON (QUERYID, ACCTSTRINGDATE)
DR_QRY_LOG_EXP_HST_IDX       ON (QUERYID, ACCTSTRINGDATE)

-- 缺少：DR_QRY_LOG_EXP_HST 在 STATEMENT_ID 上的索引
-- 因與 TGT 表以 STATEMENT_ID + ACCTSTRINGDATE 聯結

解決方案：新增本地分區索引

CREATE INDEX dr_qry_log_exp_hst_idx2
  ON gv.dr_qry_log_exp_hst (statement_id, acctstringdate) LOCAL;

優化後執行計畫關鍵變化

• 最內層 NESTED LOOP 改用 INDEX RANGE SCAN (DR_QRY_LOG_EXP_HST_IDX2)（Id 9）。
• TABLE ACCESS BY LOCAL INDEX ROWID 取代全表掃描。
• Cost 從 8654 降至 4838。

效能對比

核心心法

1. 分區表聯結時，聯結鍵必須包含分區鍵，否則無法分區剪枝。
2. 檢查每張表在聯結條件欄位上是否有本地分區索引覆蓋。
3. NESTED LOOP 適用於「小驅動大 + 索引支援」場景，否則考慮 HASH JOIN。