PL/SQL 大表優化實戰：大表變小表、小表再聯接，從 1 小時降至 112 秒

業務場景

日誌表 LOG 約 300 萬筆，主鍵 LOGTIME。需求：統計「近 2 小時內使用過系統的使用者，在近 3 天內的日誌筆數」。

初版查詢 (CTE + 直接聯接大表) — 執行 1 小時+

WITH result1 AS (
    SELECT DISTINCT username
    FROM log
    WHERE logtime >= SYSDATE - 2/24  -- 近 2 小時
)
SELECT log.username, COUNT(1) AS times
FROM log
INNER JOIN result1 ON log.username = result1.username
WHERE logtime >= SYSDATE - 3         -- 近 3 天
GROUP BY log.username;

問題：優化器選擇 NESTED LOOPS，以小結果集 result1 驅動大表 LOG 全表掃描 300 萬 × 使用者數，極慢。

嘗試 1：強制 HASH JOIN — 有改善但未達預期

SELECT /*+ USE_HASH(log) */ log.username, COUNT(1) AS times
FROM log
INNER JOIN result1 ON log.username = result1.username
WHERE logtime >= SYSDATE - 3
GROUP BY log.username;

HASH JOIN 減少隨機 I/O，但仍需掃描 3 天完整大表再聯接。

關鍵洞察：先聚合再聯接 (大表變小表)

觀察發現：直接查詢 3 天聚合結果極快 (全表掃描一次即得)。瓶頸在於「先找 2 小時使用者，再回大表聯接」。反向思維：先對大表聚合成小表，再與小結果集聯接。

優化版：雙 CTE 先聚合再聯接 — 112 秒

WITH 
result1 AS (  -- 近 2 小時活躍使用者 (小)
    SELECT DISTINCT username
    FROM log
    WHERE logtime >= SYSDATE - 2/24
),
result2 AS (  -- 近 3 天各使用者筆數 (小，聚合後)
    SELECT username, COUNT(1) AS times
    FROM log
    WHERE logtime >= SYSDATE - 3
    GROUP BY username
)
SELECT result2.username, result2.times
FROM result1
INNER JOIN result2 ON result1.username = result2.username;

進一步微調：指定 HASH JOIN 建表端 — 再快一點

SELECT /*+ USE_HASH(result1) */ result2.username, result2.times
FROM result1
INNER JOIN result2 ON result1.username = result2.username;

讓較小的 result1 當 Build Input，減少記憶體佔用。

效能對比

核心原則總結

1. 大表變小表：對大表先做 GROUP BY/去重/過濾，產生小中間結果集。
2. 小表再聯接：小結果集之間聯接，避免大表參與聯接。
3. 選擇正確 JOIN 算子：HASH JOIN 適合大大表/大小表無索引；NESTED LOOPS 適合小驅動大有索引；MERGE JOIN 適合已排序輸入。
4. 觀察執行計畫：關注 Cardinality Estimation、Join Type、Access Path (Full Scan vs Index Seek)。

適用場景

• 大表 (百萬+) 需依條件過濾後再聯接/聚合。
• 過濾條件選擇率低 (結果集小) 但聯接欄位無索引或索引選擇率差。
• 歷史資料分析、報表查詢、離線 ETL。

結語

「大表變小表，小表再聯接」是大數據量查詢優化的核心模式。透過 CTE/暫存表/物化視圖將大表預先縮減，再進行精準聯接，往往能獲得數量級效能提升。本案從 1 小時降至 112 秒，證明了重構查詢邏輯優於單純 Hint 強制的價值。