問題 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
WHERE src.acctstringdate > SYSDATE - 30
AND rl.acctstringdate > SYSDATE - 30
AND tgt.acctstringdate > SYSDATE - 30
AND NOT (UPPER(tgt.systemname)=UPPER(src.systemname)
AND UPPER(tgt.databasename)=UPPER(src.databasename)
AND UPPER(tgt.tablename)=UPPER(src.tablename))
AND tgt.systemname IS NOT NULL
AND tgt.databasename IS NOT NULL
AND tgt.tablename IS NOT NULL;
執行計畫問題
- 三表皆為百萬級分區表 (依
acctstringdate 分區)。
- 計畫顯示
TABLE ACCESS FULL 於 DR_QRY_LOG_EXP_HST,未利用分區裁剪。
- Cost 8654,執行時間 60+ 秒。
解決方案:建立本地分區索引
CREATE INDEX idx_dr_qry_log_exp_hst_acct
ON dr_qry_log_exp_hst (acctstringdate, queryid, systemname)
LOCAL; -- 本地分區索引,與表分區對齊
效果
- 分區裁剪生效,僅掃描最近 30 天分區。
- 索引鍵覆蓋 JOIN 條件,避免回表。
- Cost 大幅下降,執行時間 < 5 秒。
關鍵點
- 分區表 JOIN 時,連接鍵**必須包含分區鍵**才能觸發 Partition Pruning。
- 本地索引維護成本低,Drop/Truncate Partition 自動同步索引分區。
- 並行度 (PARALLEL 8) 配合索引存取,進一步縮短回應時間。