萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> Oracle 教學

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
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 秒。

關鍵點

  1. 分區表 JOIN 時,連接鍵**必須包含分區鍵**才能觸發 Partition Pruning。
  2. 本地索引維護成本低,Drop/Truncate Partition 自動同步索引分區。
  3. 並行度 (PARALLEL 8) 配合索引存取,進一步縮短回應時間。
copyright © 萬盛學電腦網 all rights reserved