跨資料庫 UNION 查詢實戰：多源數據統計整合方案

場景描述

兩個不同 Schema/資料庫 {4}、{5} 存放相似結構的工單資料，需統計指定時間區間 {1}~{2}、部門 {3} 的「未解決」與「已解決」數量。

原始查詢優化重點

• 移除矛盾條件：c809000021<>'已關閉' AND c809000021='已關閉' 互斥，需確認業務邏輯
• 統一欄位別名：c809000012 vs c811020016 應為同義欄位，建議建立 View 統一介面
• 使用 UNION ALL 替代 UNION：避免排序去重，效能提升 50%+
• 參數化：{1}~{5} 應使用 Bind Variable 防注入

重構後的標準查詢

WITH params AS (
  SELECT :start_dt AS start_dt, :end_dt AS end_dt, :dept AS dept FROM dual
),
src_a AS (
  SELECT '未解決' AS protype, COUNT(DISTINCT c1) AS procount
  FROM {4} t1, params p
  WHERE t1.c700000006 BETWEEN p.start_dt AND p.end_dt
    AND t1.c809000012 = p.dept
    AND t1.c809000021 != '已關閉'
    AND t1.c809000001 IS NOT NULL
  UNION ALL
  SELECT '未解決', COUNT(DISTINCT c1)
  FROM {5} t2, params p
  WHERE t2.c700000006 BETWEEN p.start_dt AND p.end_dt
    AND t2.c811020016 = p.dept
    AND t2.c700000010 != '已關閉'
    AND t2.c811020001 IS NOT NULL
  UNION ALL
  SELECT '已解決', COUNT(DISTINCT c1)
  FROM {4} t3, params p
  WHERE t3.c700000006 BETWEEN p.start_dt AND p.end_dt
    AND t3.c809000012 = p.dept
    AND t3.c809000021 = '已關閉'
    AND t3.c809000001 IS NOT NULL
  UNION ALL
  SELECT '已解決', COUNT(DISTINCT c1)
  FROM {5} t4, params p
  WHERE t4.c700000006 BETWEEN p.start_dt AND p.end_dt
    AND t4.c811020016 = p.dept
    AND t4.c700000010 = '已關閉'
    AND t4.c811020001 IS NOT NULL
)
SELECT protype, SUM(procount) AS total_count
FROM src_a
GROUP BY protype
ORDER BY protype;

架構層面建議

1. 建立統一 View：在各源端建立 V_TICKET_STATS 統一欄位名（ticket_id, status, dept_code, create_dt, category）
2. DB Link 整合：透過 Database Link 將遠端 View 映射為本地同義詞，查詢如同單表
3. 物化視圖預聚合：每日刷新物化視圖 MV_DAILY_TICKET_STATS，報表直接查詢 MV，秒級回應
4. 分區表：按 create_dt 範圍分區，歷史資料自動歸檔，查詢自動分區裁剪

效能對比