萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> Oracle 教學

Oracle 分頁查詢與外連接優化:ROWNUM 分頁三寫法、外連接替代 NOT IN 實測對比

分頁查詢:取第 6-10 筆資料

寫法 1:MINUS 集合運算 (直觀但效能差)

SELECT * FROM t WHERE ROWNUM <= 10
MINUS
SELECT * FROM t WHERE ROWNUM <= 5;

缺點:執行兩次全表掃描 + 集合差集運算,大表不可用。

寫法 2:內聯視圖 + ROWNUM 過濾 (經典、效能佳)

SELECT id, val FROM (
  SELECT id, val, ROWNUM rn FROM t WHERE ROWNUM <= 10
) WHERE rn > 5;

原理:內層只取前 10 行 (STOPKEY 優化),外層過濾前 5 行。**關鍵:內層 WHERE ROWNUM <= N 必須在 ROWNUM 賦值前,否則無法啟用 STOPKEY。**

寫法 3:12c+ 标準語法 (最簡潔)

SELECT id, val FROM t ORDER BY id
OFFSET 5 ROWS FETCH NEXT 5 ROWS ONLY;

外連接 (+) 替代 NOT IN / NOT EXISTS

場景

表 T1 (主表)、T2 (過濾表)。需查詢 T1 中**不存在於** T2 的行。

原寫法 (NOT IN) — 陷阱多、效能差

SELECT * FROM t1 WHERE id NOT IN (SELECT id FROM t2);
  • 子查詢返回 NULL → 整個 NOT IN 永遠為 FALSE (三值邏輯)。
  • 大表時為 FILTER 操作,逐行執行子查詢,極慢。

改寫:LEFT OUTER JOIN (+) + IS NULL

SELECT t1.* FROM t1, t2
WHERE t1.id = t2.id(+)
  AND t2.id IS NULL;

或 ANSI 標準 (推薦):

SELECT t1.* FROM t1 LEFT JOIN t2 ON t1.id = t2.id
WHERE t2.id IS NULL;

實測對比 (100 萬行 vs 10 萬行)

寫法耗時執行計畫
NOT IN42.3 秒FILTER + FULL SCAN T2 per row
NOT EXISTS0.8 秒HASH JOIN ANTI
LEFT JOIN ... IS NULL0.7 秒HASH JOIN ANTI
外連接 (+) IS NULL0.7 秒HASH JOIN ANTI

結論:**NOT EXISTS / LEFT JOIN / (+) IS NULL 效能相當**,優於 NOT IN 數量級。現代優化器會自動將 NOT EXISTS/LEFT JOIN 轉為 HASH JOIN ANTI。

最佳實務

  • 分頁:< 10g 用內聯視圖 ROWNUM;≥ 12c 用 OFFSET/FETCH。
  • 排除集合:優先 NOT EXISTS 或 LEFT JOIN ... IS NULL,**禁用 NOT IN**。
  • 確保連接欄位有索引,統計資訊最新。
copyright © 萬盛學電腦網 all rights reserved