Oracle 分頁與排序查詢四大經典語句模式

核心差異：ROWNUM vs ROW_NUMBER()

SQL Server 可直接用 TOP + ROW_NUMBER() OVER；Oracle 僅有 ROWNUM 偽欄位，且 ROWNUM 在 WHERE 條件評估前產生，導致無法直接用 ROWNUM BETWEEN 11 AND 20，必須採用雙層巢狀查詢。

四大經典模式

1. 取前 N 筆 (最簡單)

SELECT *
FROM   test_table
WHERE  ROWNUM <= 10;

2. 取第 M~N 筆 (基本分頁)

SELECT *
FROM   (SELECT test_table.*, ROWNUM rn FROM test_table WHERE ROWNUM <= 20)
WHERE  rn > 10;

3. 排序後取前 N 筆

SELECT *
FROM   (SELECT * FROM test_table ORDER BY name ASC)
WHERE  ROWNUM <= 10;

4. 排序後取第 M~N 筆 (最完整分頁)

SELECT *
FROM   (SELECT tt.*, ROWNUM rn
        FROM   (SELECT * FROM test_table ORDER BY name ASC) tt
        WHERE  ROWNUM <= 20)
WHERE  rn > 10;

關鍵原理解析

• 內層 ORDER BY：確保排序在 ROWNUM 產生前完成。
• 中層 ROWNUM <= 結束行：限制掃描範圍，避免全表排序後再丟棄。
• 外層 RN > 起始行：過濾出目標頁面。

Oracle 12c+ 簡化寫法 (標準 SQL)

SELECT *
FROM   test_table
ORDER  BY name ASC
OFFSET 10 ROWS FETCH NEXT 10 ROWS ONLY;

優點：語意清晰、優化器可直接最佳化、支援 FIRST_ROWS(n) 提示。生產環境建議優先使用。

效能避坑指南

• 深分頁效能劣化：OFFSET 100000 仍需掃描前 10 萬列。大資料量改用 Keyset Pagination (WHERE id > :last_id ORDER BY id FETCH FIRST 10 ROWS ONLY)。
• 索引支援排序：ORDER BY 欄位應有索引，避免 SORT ORDER BY 操作。
• 避免 SELECT *：僅選取必要欄位，減少網路傳輸與記憶體排序壓力。