SELECT * FROM t WHERE ROWNUM <= 10
MINUS
SELECT * FROM t WHERE ROWNUM <= 5;
缺點:執行兩次全表掃描 + 集合差集運算,大表不可用。
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。**
SELECT id, val FROM t ORDER BY id
OFFSET 5 ROWS FETCH NEXT 5 ROWS ONLY;
表 T1 (主表)、T2 (過濾表)。需查詢 T1 中**不存在於** T2 的行。
SELECT * FROM t1 WHERE id NOT IN (SELECT id FROM t2);
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;
| 寫法 | 耗時 | 執行計畫 |
|---|---|---|
| NOT IN | 42.3 秒 | FILTER + FULL SCAN T2 per row |
| NOT EXISTS | 0.8 秒 | HASH JOIN ANTI |
| LEFT JOIN ... IS NULL | 0.7 秒 | HASH JOIN ANTI |
| 外連接 (+) IS NULL | 0.7 秒 | HASH JOIN ANTI |
結論:**NOT EXISTS / LEFT JOIN / (+) IS NULL 效能相當**,優於 NOT IN 數量級。現代優化器會自動將 NOT EXISTS/LEFT JOIN 轉為 HASH JOIN ANTI。
NOT EXISTS 或 LEFT JOIN ... IS NULL,**禁用 NOT IN**。