Oracle 分頁查詢：正確排序與高效能寫法解析

問題現象

原始查詢：

SELECT ROWNUM rn, t.id, o.name yyb, u.name xm, t.mc
  FROM t_zdysx t, tuser u, lborganization o
 WHERE t.cjr = u.id AND u.orgid = o.orgcode AND t.gx = 1
 ORDER BY t.id;

結果 ID 順序：49, 96, 102, 105, 106, 107, 108, 109, 110, 111, 112, 113, 114, 115, 118, 119, 120, 123, 124, 126, 127, 128。

錯誤寫法：外層過濾 ROWNUM

SELECT * FROM (
  SELECT ROWNUM rn, t.id, o.name yyb, u.name xm, t.mc
    FROM t_zdysx t, tuser u, lborganization o
   WHERE t.cjr = u.id AND u.orgid = o.orgcode AND t.gx = 1
   ORDER BY t.id
) WHERE rn > 10 AND rn <= 20;

**結果錯誤**：先 ORDER BY 再賦予 ROWNUM，但外層過濾在**已排序結果集**上截取，邏輯看似正確，實則 **CBO 無法將外層 ROWNUM 條件下推**，導致內層**全量排序後再過濾**，效能極差。

正確高效寫法：雙層嵌套 + 內層 ROWNUM 截斷

SELECT *
  FROM (
    SELECT ROWNUM rn, ta.*
      FROM (
        SELECT t.id, o.name yyb, u.name xm, t.mc
          FROM t_zdysx t, tuser u, lborganization o
         WHERE t.cjr = u.id AND u.orgid = o.orgcode AND t.gx = 1
         ORDER BY t.id
      ) ta
     WHERE ROWNUM <= 20
  )
 WHERE rn > 10;

**結果正確**：ID 112~126，符合預期第 11-20 筆。

原理深度解析

關鍵差異：WHERE ROWNUM <= N 可下推

• CBO 可將 **第二層** 的 WHERE ROWNUM <= 20 **下推至最內層查詢**。
• 內層查詢執行時，**一旦產出 20 筆排序後的行即停止**（COUNT STOPKEY 操作），避免全量排序。
• 外層 WHERE rn > 10 僅對 20 筆資料過濾，開銷極小。

錯誤寫法為何無法下推

• 過濾條件 rn > 10 AND rn <= 20 位於**第三層**，引用別名 rn（內層不認識）。
• CBO 無法跨層下推別名條件 → 內層**全量執行排序** → 中間層傳遞全量 → 外層過濾。

效能對比

通用分頁模板

SELECT *
  FROM (
    SELECT ROWNUM rn, t.*
      FROM (  -- 核心查詢：含 ORDER BY
        SELECT col1, col2, ...
          FROM table_a a, table_b b
         WHERE a.id = b.a_id AND a.status = 'ACTIVE'
         ORDER BY a.created_dt DESC
      ) t
     WHERE ROWNUM <= :page_size * :page_no
  )
 WHERE rn > :page_size * (:page_no - 1);

• :page_size 每頁筆數，:page_no 頁碼 (1-based)。
• 單表、多表、複雜連接同樣適用。

12c+ 新語法（更簡潔）

SELECT col1, col2, ...
  FROM table_a a, table_b b
 WHERE a.id = b.a_id AND a.status = 'ACTIVE'
 ORDER BY a.created_dt DESC
 OFFSET :offset ROWS FETCH NEXT :page_size ROWS ONLY;

內部同樣最佳化為 Top-N 排序，**優先使用 12c+ 標準語法**。

總結

分頁核心：**內層 ORDER BY + 中層 ROWNUM <= 截斷 + 外層 RN > 起始**。利用 CBO STOPKEY 下推特性，實現 **Top-N 排序**，避免全量排序與大結果集傳輸，效能提升數十倍。