Oracle 分頁查詢最佳實踐：OFFSET/FETCH (12c+)、ROWNUM 傳統寫法與存儲過程封裝

現代標準寫法 (12c+)

SELECT * 
FROM employees
ORDER BY hire_date DESC
OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY;

• OFFSET n ROWS：跳過前 n 筆
• FETCH NEXT m ROWS ONLY：取後續 m 筆
• 符合 ANSI SQL:2008，可攜性高、最佳化器支援佳

傳統 ROWNUM 寫法 (11g 及以下)

標準模式

SELECT * FROM (
  SELECT a.*, ROWNUM rn
  FROM (
    SELECT * FROM employees ORDER BY hire_date DESC
  ) a
  WHERE ROWNUM <= :offset + :limit
) WHERE rn > :offset;

• 內層排序 → 中層賦予 ROWNUM → 外層篩選範圍
• 關鍵：中層 WHERE ROWNUM <= upper_bound 讓最佳化器提早停止 (COUNT STOPKEY)

效能陷阱

-- ❌ 錯誤：外層排序導致全表掃描
SELECT * FROM (
  SELECT * FROM employees ORDER BY hire_date DESC
) WHERE ROWNUM BETWEEN 21 AND 30;

存儲過程封裝 (通用分頁元件)

CREATE OR REPLACE PACKAGE pkg_pagination AS
  TYPE ref_cursor IS REF CURSOR;
  
  PROCEDURE page_query (
    p_table       IN  VARCHAR2,       -- 表名 (含 Schema)
    p_where       IN  VARCHAR2 DEFAULT NULL,  -- WHERE 條件 (不含 WHERE)
    p_order_by    IN  VARCHAR2 DEFAULT NULL,  -- ORDER BY 子句
    p_page_num    IN  NUMBER  DEFAULT 1,      -- 第 N 頁 (1-based)
    p_page_size   IN  NUMBER  DEFAULT 20,     -- 每頁筆數
    p_total_rows  OUT NUMBER,                 -- 總筆數
    p_total_pages OUT NUMBER,                 -- 總頁數
    p_result      OUT ref_cursor              -- 結果游標
  );
END pkg_pagination;
/

CREATE OR REPLACE PACKAGE BODY pkg_pagination AS
  PROCEDURE page_query (...) IS
    v_sql      VARCHAR2(4000);
    v_cnt_sql  VARCHAR2(4000);
    v_offset   NUMBER;
    v_limit    NUMBER;
  BEGIN
    -- 參數驗證
    IF p_page_num < 1 THEN p_page_num := 1; END IF;
    IF p_page_size < 1 THEN p_page_size := 20; END IF;
    
    v_offset := (p_page_num - 1) * p_page_size;
    v_limit  := p_page_size;
    
    -- 總筆數
    v_cnt_sql := 'SELECT COUNT(*) FROM ' || p_table;
    IF p_where IS NOT NULL THEN
      v_cnt_sql := v_cnt_sql || ' WHERE ' || p_where;
    END IF;
    EXECUTE IMMEDIATE v_cnt_sql INTO p_total_rows;
    
    -- 總頁數
    p_total_pages := CEIL(p_total_rows / p_page_size);
    IF p_page_num > p_total_pages THEN
      p_page_num := p_total_pages;
      v_offset := (p_page_num - 1) * p_page_size;
    END IF;
    
    -- 分頁查詢 (12c+ 使用 OFFSET/FETCH)
    v_sql := 'SELECT * FROM ' || p_table;
    IF p_where IS NOT NULL THEN
      v_sql := v_sql || ' WHERE ' || p_where;
    END IF;
    IF p_order_by IS NOT NULL THEN
      v_sql := v_sql || ' ORDER BY ' || p_order_by;
    END IF;
    v_sql := v_sql || ' OFFSET ' || v_offset || ' ROWS FETCH NEXT ' || v_limit || ' ROWS ONLY';
    
    OPEN p_result FOR v_sql;
  END page_query;
END pkg_pagination;
/

-- 呼叫範例
VAR rc REFCURSOR
EXEC pkg_pagination.page_query('EMPLOYEES', 'department_id=10', 'hire_date DESC', 2, 10, :total_rows, :total_pages, :rc)
PRINT rc

進階優化技巧

Keyset Pagination (游標分頁) - 大資料集首選

-- 第一頁
SELECT * FROM employees 
WHERE department_id = 10 
ORDER BY hire_date DESC, employee_id DESC
FETCH FIRST 20 ROWS ONLY;

-- 後續頁 (記住上一頁最後一筆鍵值)
SELECT * FROM employees 
WHERE department_id = 10 
  AND (hire_date, employee_id) < (:last_hire_date, :last_emp_id)
ORDER BY hire_date DESC, employee_id DESC
FETCH FIRST 20 ROWS ONLY;

• 無 OFFSET 掃描開銷，效能恆定
• 需唯一排序鍵 (含 PK 避免重複值)
• 不支援隨機跳頁，適合無限捲動/分頁器

物化視圖預分頁 (極大靜態資料)

CREATE MATERIALIZED VIEW mv_emp_paged
BUILD IMMEDIATE REFRESH FAST ON COMMIT
AS SELECT *, ROW_NUMBER() OVER (ORDER BY hire_date DESC) AS rn
FROM employees;

-- 查詢
SELECT * FROM mv_emp_paged WHERE rn BETWEEN 21 AND 30;

Java/JDBC 呼叫範例

String sql = "{call pkg_pagination.page_query(?,?,?,?,?,?,?,?)}";
CallableStatement cs = conn.prepareCall(sql);
cs.setString(1, "EMPLOYEES");
cs.setString(2, "department_id=10");
cs.setString(3, "hire_date DESC");
cs.setInt(4, 2);
cs.setInt(5, 10);
cs.registerOutParameter(6, Types.INTEGER);   // total_rows
cs.registerOutParameter(7, Types.INTEGER);   // total_pages
cs.registerOutParameter(8, OracleTypes.CURSOR);
cs.execute();

int totalRows = cs.getInt(6);
int totalPages = cs.getInt(7);
ResultSet rs = (ResultSet) cs.getObject(8);
while (rs.next()) { /* process */ }

方案選型指南