Oracle ROWNUM 分頁查詢陷阱與正確姿勢：排序先於 ROWNUM、子查詢隔離、效能優化

Oracle ROWNUM 分頁查詢陷阱與正確姿勢：排序先於 ROWNUM、子查詢隔離、效能優化

ROWNUM 是 Oracle 為結果集動態分配的偽列，分配時機在 WHERE 條件過濾之後、ORDER BY 排序之前。這一機制導致直接在外層使用 ROWNUM 配合 ORDER BY 會產生錯誤結果。本文深度解析 ROWNUM 運作原理、分頁正確寫法、> / >= / BETWEEN 失效原因、效能優化特性。

一、ROWNUM 核心機制：分配順序決定一切

執行順序:
1. FROM / WHERE 子句執行，產生候選行集
2. 對候選行集依次分配 ROWNUM (從 1 開始遞增)
3. SELECT 列表投影
4. GROUP BY / HAVING
5. ORDER BY 排序 (此時 ROWNUM 已固定)

關鍵推論：ROWNUM 永遠反映「未排序結果集中的相對位置」。若先 ORDER BY 再取 ROWNUM，必須用子查詢先排序，外層再套用 ROWNUM。

二、經典錯誤：外層 ORDER BY 導致分頁錯亂

-- 錯誤寫法：先取前 20 行，再排序 → 取得的是前 20 行未排序子集的排序，非全表排序前 20
SELECT * FROM (
  SELECT r.*, ROWNUM RN FROM (SELECT * FROM tbrole) r WHERE ROWNUM <= 20
) WHERE RN >= 1 ORDER BY CREATE_TIME ASC;

-- 正確寫法：內層先排序，中層套 ROWNUM，外層篩選 RN 範圍
SELECT * FROM (
  SELECT r.*, ROWNUM RN FROM (
    SELECT * FROM tbrole ORDER BY CREATE_TIME ASC
  ) r WHERE ROWNUM <= 20
) WHERE RN >= 1;  -- 第 1 頁 (1-20)

-- 第 2 頁 (21-40)
SELECT * FROM (
  SELECT r.*, ROWNUM RN FROM (
    SELECT * FROM tbrole ORDER BY CREATE_TIME ASC
  ) r WHERE ROWNUM <= 40
) WHERE RN > 20;

三、ROWNUM 比較運算子陷阱

解決：必須用子查詢先將 ROWNUM 物化為列 (RN)，外層再用 RN > 10 等條件篩選。

四、ROWUM 效能優化特性：避免磁盤排序

• 指定 ROWNUM <= N 時，Oracle 知道只需前 N 行，排序操作可在記憶體中完成 (Top-N Sort)，避免大數據集寫入臨時表空間進行磁盤排序。
• ROWNUM 無法避免全表掃描 (若無索引支撐 WHERE 條件)，但能大幅降低排序資源消耗。
• 12c+ OFFSET ... ROWS FETCH NEXT ... ROWS ONLY 語法內部同樣利用 Top-N 優化，語法更標準。

五、分頁最佳實踐模板 (通用)

SELECT * FROM your_table
WHERE your_conditions
ORDER BY your_sort_columns
OFFSET (:page_no - 1) * :page_size ROWS
FETCH NEXT :page_size ROWS ONLY;

核心口訣：先排序，再 ROWNUM，最後篩選 RN。三層巢狀結構 (最內排序 → 中層 ROWNUM 截斷 → 外層 RN 範圍) 是 Oracle 11g 及以前分頁的鐵律。12c+ 請直接使用 ANSI 標準 OFFSET/FETCH，可讀性與可移植性雙贏。

1. 上一篇文章： Oracle 自動維護任務狀態檢查與關閉驗證：DBA_AUTOTASK_CLIENT 查詢指南
2. 下一篇文章： Oracle 細粒度審計 (FGA) 實戰部署：SCOTT.EMP 表增刪改監控完整腳本

Oracle 教學

Oracle SQL 基礎入門：使用者、表空間、建表與查詢實戰

前言 本文針對 Oracle 初學者，系統整理安裝驗證、使用

Oracle 階層式查詢 START WITH ... CONNECT BY 完整詳解

語法核心 SELECT * FROM table_name

Linux 下 Oracle 11g RAC 搭建（十）：使用 DBCA 建立資料庫

在完成 Grid Infrastructure 與 Orac

Windows 10 x64 環境安裝 Oracle XE 11g R2 完整指南：字符集修改、APEX 部署與端口調整

Windows 10 x64 環境安裝 Oracle XE