SQL Server 與 Oracle 分頁查詢三大模式比較：臨時表、物件陣列、集合式 TOP/ROWNUM 最佳實踐

分頁核心需求

取得第 1000-1010 筆資料（頁面大小 10，第 100 頁）。

模式一：臨時表法（資源消耗大）

1. 依排序建立臨時表。
2. 插入前 1010 筆資料。
3. 反向排序臨時表。
4. 取前 10 筆顯示。
5. 刪除臨時表。

缺點：建立/銷毀臨時表開銷大，且需傳輸 1010 筆資料至應用層。

模式二：應用層物件陣列法（稍優但仍傳輸過多資料）

1. 查詢前 1010 筆資料。
2. 應用層反向取後 10 筆存入物件陣列。
3. 反轉陣列顯示。

缺點：同樣傳輸 1010 筆資料，網路頻寬浪費。

模式三：資料庫端集合式分頁（最佳）

每次僅從資料庫取回頁面大小的資料塊。雖每頁需查詢，但傳輸量極小，搭配連線池省去建連開銷，資料庫端有成熟優化技術（索引、統計資料、執行計畫快取）。

SQL Server 寫法 (TOP 巢狀)

SELECT TOP 10 * FROM (
    SELECT TOP 10 * FROM (
        SELECT TOP 1010 * FROM docdetail 
        ORDER BY lastmoddate ASC, Id ASC
    ) AS temptbl1 
    ORDER BY lastmoddate DESC, Id DESC
) AS temptbl2 
ORDER BY lastmoddate ASC, Id ASC;

邏輯：最內層取前 1010 筆 (ASC) → 中層反向取前 10 筆 (DESC) → 外層再反向回正序 (ASC)。

Oracle 寫法 (ROWNUM 特性)

Oracle ROWNUM 在查詢後、排序前賦值，故需子查詢先排序再過濾：

SELECT * FROM (
    SELECT my_table.*, ROWNUM AS rn FROM (
        SELECT * FROM my_table ORDER BY sort_col
    ) my_table 
    WHERE ROWNUM <= 1010
) WHERE rn >= 1000;

現代標準寫法 (SQL Server 2012+ / Oracle 12c+)

-- SQL Server 2012+ OFFSET/FETCH
SELECT * FROM docdetail 
ORDER BY lastmoddate, Id 
OFFSET 1000 ROWS FETCH NEXT 10 ROWS ONLY;

-- Oracle 12c+ OFFSET/FETCH
SELECT * FROM my_table 
ORDER BY sort_col 
OFFSET 1000 ROWS FETCH NEXT 10 ROWS ONLY;

效能建議

• 分頁欄位務必建立覆蓋索引 (包含 ORDER BY 與 SELECT 欄位)，避免 Key Lookup/Table Access Full。
• 深分頁 (大 OFFSET) 效能下降，考慮游標式分頁 (WHERE id > @lastId ORDER BY id LIMIT 10) 適用無限捲動場景。
• 避免在大表使用 COUNT(*) 取得總筆數，考慮快取總數或估算值。