Oracle 千萬級資料高效處理實戰：避開 Hibernate、精準 SELECT、條件下推與批次寫入

痛點回顧

專案需對千萬列表執行批次插入與高頻查詢。初期嘗試 Hibernate 導致：

• 插入 5 萬列耗時 10 分鐘，瓶頸在 5 萬次 Sequence 往返 (預設 identity/sequence 策略)。
• 查詢觸發大量不必要的關聯載入，佔用海量記憶體與資料庫資源。

結論：海量資料場景拒絕 ORM，直寫 SQL / JDBC / 存儲過程才是正道。

核心優化原則

1. 精準投影：拒絕 SELECT *

-- 慢：全表掃描 + 傳輸所有欄位
SELECT * FROM huge_table;

-- 快：只取業務必要欄位，可走 Index Fast Full Scan / Covering Index
SELECT id, biz_key, amt FROM huge_table;

• 欄位越寬 → 單塊容納列數越少 → I/O 放大效應越明顯。
• Covering Index 可直接從索引滿足查詢，避免回表。

2. 條件下推：拒絕永真條件

-- 慢：WHERE name LIKE '%' 導致全表掃描 + 函數評估
SELECT COUNT(id) FROM huge_table WHERE name LIKE '%';  -- 33 秒

-- 快：移除無意義條件
SELECT COUNT(id) FROM huge_table;  -- 11 秒

• 應用層動態構建 WHERE 子句：無條件時完全不生成 WHERE。
• 對於可選查詢欄位，使用 CASE WHEN :param IS NOT NULL THEN col = :param END 或動態 SQL。

3. 批次插入最佳實踐

-- 1. 使用陣列綁定 (JDBC batch / OCI Array Bind)
-- 2. 直接取 Sequence 下一值 (無序) 或使用 GUID
-- 3. 禁用索引/約束/觸發器，載入後重建
-- 4. 並行 DML (PDML) + APPEND Hint
INSERT /*+ APPEND PARALLEL(8) */ INTO target_table
SELECT * FROM staging_table;

• APPEND：直接寫入 High Water Mark 以上，最小化 Redo/Undo。
• PARALLEL：多進程並行寫入，線性縮短時間。
• 序列號預取：SELECT seq.NEXTVAL FROM dual CONNECT BY LEVEL <= 50000 一次取 5 萬值，消除網路往返。

4. 統計資訊與執行計畫穩定

• 載入後立即 DBMS_STATS.GATHER_TABLE_STATS(..., ESTIMATE_PERCENT=>DBMS_STATS.AUTO_SAMPLE_SIZE)。
• 關鍵 SQL 使用 SQL Plan Baseline 或 SQL Profile 固定優質計畫。

實測對比 (千萬列)

總結

千萬級資料處理三板斧：拒絕 ORM、精準投影、批次並行。架構層面引入 Staging Table + PDML + 統計資訊管理，可穩定支撐億級日增量。