Oracle ROWNUM 分頁查詢陷阱：為何 ORDER BY 後 ROWNUM 失效與正確子查詢寫法

常見錯誤寫法

SELECT emp_no FROM emp 
WHERE ROWNUM < 10 
ORDER BY emp_no;

失效原因

1. Oracle 先執行 WHERE 過濾 (含 ROWNUM)，取得前 N 筆原始順序資料
2. 再對結果集執行 ORDER BY 排序
3. 結果：非排序後的前 N 筆，而是排序前的前 N 筆再排序

ROWNUM 是在資料擷取階段即時產生的偽欄位，非儲存值。

正確寫法：子查詢先排序再過濾

SELECT emp_no FROM (
  SELECT emp_no FROM emp ORDER BY emp_no
) WHERE ROWNUM < 10;

• 內層查詢：完整排序產生有序結果集
• 外層查詢：對已排序結果套用 ROWNUM 過濾
• 獲得：真正排序後的前 N 筆

進階分頁查詢 (12c+ 標準語法)

-- Offset/Fetch (ANSI SQL 標準)
SELECT emp_no FROM emp 
ORDER BY emp_no 
OFFSET 0 ROWS FETCH NEXT 10 ROWS ONLY;

-- 12c 前通用寫法 (支援任意頁)
SELECT * FROM (
  SELECT a.*, ROWNUM rn FROM (
    SELECT emp_no FROM emp ORDER BY emp_no
  ) a WHERE ROWNUM <= :end_row
) WHERE rn >= :start_row;

ROWNUM 關鍵特性總結

效能提示

• 大資料量分頁：確保 ORDER BY 欄位有索引，避免全表排序
• 深分頁 (OFFSET 100000) 效能差，改用 Keyset Pagination：WHERE id > :last_id ORDER BY id FETCH NEXT 10
• 統計資訊準確有助優化器選擇最佳排序方案

1. 上一篇文章： Oracle 10g Linux 安裝前置作業完整指南：用戶、目錄、核心參數與 sysctl 配置
2. 下一篇文章： Oracle 資料庫啟動架構完整解析：實例結構、背景進程、記憶體組件與 8 種啟動模式

Oracle 資料庫基礎

Oracle 字串搜尋效能實測：INSTR 完勝 LIKE，1100 萬筆實測差距 3 倍

實測場景 測試表 T，資料量 1,100 萬筆 欄

基於 Oracle 數據倉庫的空間數據分層管理器架構設計與實作

背景與動機 面對地學海量空間數據 (數字地球/數字國土)，傳

Oracle 建立表空間、使用者與授權的標準四步驟腳本

1. 建立使用者表空間（永久） CREATE TABLESP

Oracle 記憶體管理核心：LRU 鏈、髒 LRU、檢查點隊列完整機制

一、LRU 鏈：改良版最近最少使用算法 基本結構 Buf