Oracle 分頁查詢與外連接優化 NOT IN 的實戰技巧

場景一：取得第 6~10 筆記錄 (分頁核心邏輯)

假設測試表 T(ID VARCHAR2(4) PRIMARY KEY, VALUE INT)。

方法 1：集合運算 MINUS (直觀但效能較差)

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

先取前 10 筆，再減去前 5 筆。需掃描兩次，且 MINUS 會排序去重，大數據量下效能不佳。

方法 2：子查詢 + ROWNUM 篩選 (推薦，效能較好)

SELECT ID, VALUE FROM (
  SELECT ID, VALUE, ROWNUM R FROM T WHERE ROWNUM <= 10
) WHERE R > 5;

內層限制 10 筆並賦予 ROWNUM，外層篩選 R > 5。單次掃描，無排序開銷。

場景二：用外連接替代 NOT IN / IN (大幅提升效能)

IN / NOT IN 面對萬筆以上資料時，需逐行比對，執行時間可能達半小時。替代方案：

1. EXISTS / NOT EXISTS (常用)。
2. 外連接 (+) 寫法 (本文重點)。

測試表結構

CREATE TABLE T1(ID VARCHAR2(4) PRIMARY KEY, VALUE INT);
CREATE TABLE T2(VALUE INT);

替代 IN (內連接語意)

SELECT T1.ID, T1.VALUE
FROM T1, T2
WHERE T1.VALUE = T2.VALUE(+)
  AND T2.VALUE IS NOT NULL;

Oracle 舊式外連接語法：(+) 標記在「可能找不到對應行」的一方。此處 T2.VALUE(+) 表示 T2 為外部表，找不到時為 NULL。再加 IS NOT NULL 過濾，等同內連接 / IN。

替代 NOT IN (反向語意)

SELECT T1.ID, T1.VALUE
FROM T1, T2
WHERE T1.VALUE = T2.VALUE(+)
  AND T2.VALUE IS NULL;

同理，外連接後保留 T2 為 NULL 的行，即 T1 中不存在於 T2 的值，等同 NOT IN / NOT EXISTS。

實測建議

大數據量環境下，外連接寫法執行計畫通常走 Hash Join 或 Nested Loop，效能遠優於 IN/NOT IN 的 Filter 操作。建議在批次報表、ETL 等大量集合運算場景採用。