Oracle 分區表全分區掃描問題分析與優化實戰

前言

分區表是 Oracle 資料庫中處理大量資料的核心功能，它依據特定規則將大表拆分為多個較小的分區，從而大幅提升查詢效能。然而，若 SQL 執行計畫出現「全分區掃描」而非「單分區掃描」，效能差異將極為顯著。本文將透過系統視圖查詢分區資訊，並以實戰案例示範如何避免全分區掃描。

一、快速獲取分區表與分區鍵資訊

1. 查詢當前用戶下所有分區表

SELECT table_name
FROM user_tables
WHERE partitioned = 'YES';

2. 查詢指定分區表的分區欄位

SELECT a.table_name,
       a.partitioned,
       b.partition_count,
       c.column_name
FROM user_tables a,
     user_part_tables b,
     user_part_key_columns c
WHERE a.table_name = b.table_name
  AND b.table_name = c.name
  AND c.name = UPPER('&table_name');

注意：查詢條件中的表名必須為大寫。

二、典型案例：從全分區掃描到分區修剪

問題 SQL

SELECT arc_e_cons_prc_amt.mp_name,
       arc_e_cons_snap.elec_addr
FROM arc_e_cons_prc_amt,
     sa_org,
     arc_e_cons_snap,
     e_cat_prc,
     sa_c_trade_type
WHERE (arc_e_cons_prc_amt.CALC_ID = arc_e_cons_snap.CALC_ID
       AND arc_e_cons_prc_amt.ORG_NO = sa_org.ORG_NO
       AND arc_e_cons_prc_amt.PRC_CODE = e_cat_prc.PRC_CODE
       AND arc_e_cons_prc_amt.PARA_VN = e_cat_prc.PARA_VN
       AND arc_e_cons_prc_amt.TRADE_TYPE_CODE = sa_c_trade_type.TRADE_CODE)
  AND (arc_e_cons_prc_amt.YM BETWEEN '200809' AND '200811'
       AND arc_e_cons_prc_amt.ORG_NO = '2340203');

優化後 SQL

SELECT arc_e_cons_prc_amt.mp_name,
       arc_e_cons_snap.elec_addr
FROM arc_e_cons_prc_amt,
     sa_org,
     arc_e_cons_snap,
     e_cat_prc,
     sa_c_trade_type
WHERE (arc_e_cons_prc_amt.CALC_ID = arc_e_cons_snap.CALC_ID
       AND arc_e_cons_prc_amt.ORG_NO = sa_org.ORG_NO
       AND arc_e_cons_prc_amt.PRC_CODE = e_cat_prc.PRC_CODE
       AND arc_e_cons_prc_amt.PARA_VN = e_cat_prc.PARA_VN
       AND arc_e_cons_prc_amt.TRADE_TYPE_CODE = sa_c_trade_type.TRADE_CODE)
  AND (arc_e_cons_prc_amt.YM BETWEEN '200809' AND '200811'
       AND arc_e_cons_prc_amt.ORG_NO = '2340203')
  AND arc_e_cons_snap.ORG_NO = arc_e_cons_prc_amt.ORG_NO;  -- 關鍵：補上第二個分區表的分區鍵條件

核心解析

• 涉及兩張分區表：arc_e_cons_snap 與 arc_e_cons_prc_amt
• 原 SQL 僅在 arc_e_cons_prc_amt 上給出分區鍵條件（YM、ORG_NO），導致 arc_e_cons_snap 發生全分區掃描
• 新增 arc_e_cons_snap.ORG_NO = arc_e_cons_prc_amt.ORG_NO 後，優化器可對兩張表同時進行分區修剪，COST 值大幅下降

三、效能對比指標

執行計畫中觀察 PSTART / PSTOP 欄位：

• 全分區掃描： PSTART=1, PSTOP=1048575 (KEY/KEY)
• 單分區掃描： PSTART=PSTOP=具體分區號

透過 COST 值對比可直觀量化效能提升幅度。

四、最佳實踐建議

1. 針對多表 JOIN 查詢，每張分區表都必須在 WHERE 條件中包含其分區鍵
2. 善用 EXPLAIN PLAN 驗證分區修剪是否生效
3. 定期檢視 USER_TAB_PARTITIONS 確認分區分布合理性

掌握分區表查詢優化精髓，讓大數據查詢效能提升數量級。