SQL Server 與 Oracle 分頁查詢效能大比拚：從臨時表到優化 TOP/ROWNUM

分頁演進三階段

階段 1：臨時表法（資源消耗大）

1. 依排序建立臨時表
2. 取前 N+頁大小 筆資料插入臨時表
3. 反向排序臨時表
4. 取前 頁大小 筆
5. 銷毀臨時表

缺點：建立/銷毀臨時表開銷大，網路傳輸資料量 = (頁碼×頁大小)。

階段 2：應用層物件法（減少臨時表但仍傳輸過多資料）

在應用端 (ADO Recordset/Collection) 反向取資料，同樣需先撈取前 N+頁大小 筆。

階段 3：資料庫端優化分頁（推薦）

每次僅從資料庫撈取「單頁資料量」，利用資料庫索引與優化器，網路流量極小。

SQL Server 優化寫法 (TOP 巢狀)

-- 取第 1001-1010 筆 (第 101 頁, 頁大小 10)
SELECT TOP 10 * FROM (
    SELECT TOP 10 * FROM (
        SELECT TOP 1010 * FROM docdetail
        ORDER BY lastmodidate ASC, Id ASC
    ) t1 ORDER BY lastmodidate DESC, Id DESC
) t2 ORDER BY lastmodidate ASC, Id ASC

原理：內層取前 1010 筆 → 中層倒序取後 10 筆 → 外層恢復正序。

Oracle 優化寫法 (ROWNUM)

SELECT * FROM (
    SELECT t.*, ROWNUM AS rn FROM (
        SELECT * FROM docdetail ORDER BY lastmodidate ASC, Id ASC
    ) t WHERE ROWNUM < 1010
) WHERE rn >= 1000

關鍵：Oracle ROWNUM 在排序前賦值，故需子查詢先排序再過濾。

實測效能對比

某客戶 Oracle 12 萬筆文檔：

• 臨時表法：頁面渲染 ~10 秒
• 優化分頁法：頁面渲染 2-3 秒

進階建議

• SQL Server 2012+ 直接使用 OFFSET ... FETCH NEXT。
• Oracle 12c+ 使用 OFFSET ... ROWS FETCH NEXT ... ROWS ONLY。
• 確保排序欄位有索引，避免大量排序溢寫到 tempdb。
• 連線池複用連線，省去建立連線開銷。