Oracle 50+ 表關聯視圖查詢極慢優化指南：避免視圖陷阱、索引策略、物化視圖與分頁實戰

問題診斷：為何視圖查詢一整晚無結果？

1. 視圖非儲存結果：Oracle 視圖只是儲存的查詢，每次查詢視圖都會重新執行所有 50+ 表的 JOIN 與聚合。
2. 缺乏謂詞下推：SELECT * FROM v_view WHERE owner_id = 123 往往無法將 owner_id 推入各子查詢，導致全表掃描後再過濾。
3. 重複計算：多個子查詢獨立掃描同一大表 (如 ship_base_info 出現 4 次)，IO 放大數十倍。
4. 統計資訊過期：執行計劃基於錯誤基數估算，選擇嵌套迴圈而非雜湊 JOIN。

優化策略優先序

1. 拋棄視圖，改用參數化 SQL / PL/SQL 封裝

CREATE OR REPLACE PROCEDURE get_shipowner_report(
    p_owner_id IN NUMBER,
    p_cursor OUT SYS_REFCURSOR
) AS
BEGIN
    OPEN p_cursor FOR
    WITH base AS (
        SELECT owner_id, COUNT(*) cnt, SUM(dwt) dwt_sum, SUM(cgt) cgt_sum
        FROM ship_base_info
        WHERE owner_id = p_owner_id  -- 謂詞下推到最底層
        GROUP BY owner_id
    ),
    new_orders AS (
        SELECT owner_id, COUNT(*) cnt, SUM(dwt) dwt_sum
        FROM order_info_new
        WHERE owner_id = p_owner_id
          AND contract_date >= TRUNC(SYSDATE, 'YYYY')
        GROUP BY owner_id
    )
    -- 以此類推，最後 JOIN 各 CTE
    SELECT ...
    FROM base
    LEFT JOIN new_orders USING (owner_id)
    ...;
END;

優點：參數化 讓優化器產生最佳計劃、CTE 避免重複掃描、單一掃描 替代多子查詢。

2. 必須用視圖 → 物化視圖 + 快速重新整理

CREATE MATERIALIZED VIEW mv_shipowner_report
BUILD IMMEDIATE
REFRESH FAST ON COMMIT  -- 或排程 REFRESH COMPLETE
ENABLE QUERY REWRITE
AS SELECT ...;  -- 同原視圖 SQL

-- 必須建立 MV LOG
CREATE MATERIALIZED VIEW LOG ON ship_base_info WITH ROWID, SEQUENCE (owner_id, dwt, cgt) INCLUDING NEW VALUES;
-- 其他表同理

查詢直接讀取預先計算結果，毫秒級回應。

3. 索引策略：針對 JOIN 鍵與過濾欄位

• 所有 owner_id、ship_owner_id、sellers_id、buyer_id 建立 B-tree 索引
• 日期欄位 contract_date、ship_business_date 建立索引，配合 TRUNC(SYSDATE, 'YYYY') 使用函數索引
• 複合索引：(owner_id, contract_date) 覆蓋「特定船東當年新造船」查詢

4. 執行計劃分析：善用 EXPLAIN PLAN + DBMS_XPLAN

EXPLAIN PLAN FOR SELECT * FROM v_shipowner_all_test WHERE owner_id = 123;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
-- 關注：FULL TABLE SCAN、CARDINALITY 偏差、JOIN METHOD (NESTED LOOPS vs HASH JOIN)

架構層面建議

• 資料倉庫分層：ODS → DWD (明細) → DWS (主題聚合) → ADS (報表)，每層物化，避免臨時大寬表。
• 增量計算：每日只處理變更資料，合併至 DWS/ADS，而非全量重跑。
• 快取層：Redis 快取高頻報表查詢結果，DB 做後備。