Oracle 實現 SELECT TOP N 查詢：ROWNUM + ORDER BY 組合模式完整解析

Oracle 無 TOP 關鍵字，如何實現「取前 N 筆」？

SQL Server/MySQL 使用 TOP N / LIMIT N，Oracle 需結合 ROWNUM 與 ORDER BY。本文詳細說明標準模式、排序陷阱、分頁擴展與 12c+ 新語法。

核心模式：內層排序 + 外層 ROWNUM 過濾

SELECT COL1, COL2, ... FROM (
    SELECT COL1, COL2, ... FROM TABLE_NAME ORDER BY COL1, COL2 ...
) WHERE ROWNUM <= N;

完整範例：客戶表按名稱字母序取前 3 筆

資料：

ID  NAME
01  FIRST
02  SECOND
03  THIRD
04  FORTH
05  FIFTH
06  SIXTH
07  SEVENTH
08  EIGHTH
09  NINTH
10  TENTH
11  LAST

查詢：

SELECT * FROM (
    SELECT * FROM CUSTOMER ORDER BY NAME
) WHERE ROWNUM <= 3;

結果 (按 NAME 排序後前 3 筆)：

ID  NAME
08  EIGHTH
05  FIFTH
01  FIRST

為什麼需要子查詢？

ROWNUM 在 WHERE 階段分配，若寫成：

-- 錯誤：先取前 3 筆 (未排序) 再排序
SELECT * FROM CUSTOMER WHERE ROWNUM <= 3 ORDER BY NAME;

會先任意取 3 筆資料，再對這 3 筆排序 → 結果不正確。必須先排序 (子查詢)，再套用 ROWNUM (外層)。

進階：帶排序的分頁 (第 M+1 到 M+N 筆)

SELECT * FROM (
    SELECT T.*, ROWNUM RN FROM (
        SELECT * FROM CUSTOMER ORDER BY NAME
    ) T WHERE ROWNUM <= 20
) WHERE RN > 10;

Oracle 12c+ 標準語法 (OFFSET/FETCH)

-- 前 3 筆
SELECT * FROM CUSTOMER ORDER BY NAME FETCH FIRST 3 ROWS ONLY;

-- 第 11-20 筆
SELECT * FROM CUSTOMER ORDER BY NAME OFFSET 10 ROWS FETCH NEXT 10 ROWS ONLY;

• 語法簡潔、符合 ANSI SQL:2008
• 內部仍轉換為 ROWNUM 計畫，效能相當
• 支援 WITH TIES 處理並列值

效能優化建議

1. 索引支援排序：CREATE INDEX IDX_CUST_NAME ON CUSTOMER(NAME); 避免全表排序
2. FIRST_ROWS Hint：SELECT /*+ FIRST_ROWS(10) */ ... 引導優化器選擇 Index Range Scan
3. 避免 SELECT *：僅選取需要欄位，減少排序緩衝區壓力
4. 大表深分頁：改用 Keyset Pagination (WHERE NAME > :last_name ORDER BY NAME FETCH FIRST 10)

常見誤區總結