翻頁瀏覽結果

用管線化 top-N 查詢高效取得第一頁之後，通常還需要另一段查詢來抓下一頁。隨之而來的挑戰是：如何跳過前面幾頁的列。有兩種方法：

偏移法（offset method）：從頭開始為列編號，再用該編號篩掉目標頁之前的列。
尋位法（seek method）：找到前一頁的最後一筆，只抓它之後的列。

偏移法#

偏移法使用得更廣泛，主要優勢是極易處理——尤其在有專用關鍵字（offset）的資料庫上。這個關鍵字甚至已被納入 SQL 標準，成為 fetch first 擴充的一部分。

MySQL / PostgreSQL（offset 先丟棄指定列數，limit 後套用）

SELECT *
  FROM sales
 ORDER BY sale_date DESC
 LIMIT 10 OFFSET 10;

Oracle Database：ROWNUM 偽欄位無法套用 >= 篩選，必須先用別名把列號「具體化」：

SELECT *
  FROM ( SELECT tmp.*, rownum rn
           FROM ( SELECT *
                    FROM sales
                   ORDER BY sale_date DESC
                ) tmp
          WHERE rownum <= 20
       )
 WHERE rn > 10;

注意：下界用別名 RN，上界用 ROWNUM 偽欄位本身。

PostgreSQL（只接受不帶 rows 關鍵字的 offset；舊的 limit/offset 語法依然可用）

SELECT *
  FROM sales
 ORDER BY sale_date DESC
OFFSET 10
 FETCH NEXT 10 ROWS ONLY;

SQL Server（2012 起支援 fetch first；雖然標準把 offset 定為選用附加，SQL Server 卻強制要求）

SELECT *
  FROM sales
 ORDER BY sale_date DESC
OFFSET 10 ROWS
 FETCH NEXT 10 ROWS ONLY;

偏移法的兩個缺點#

除了簡單之外，另一個優點是只要一個列偏移量就能抓任意頁。但資料庫必須從頭數過所有列才能抵達目標頁。

翻的頁數越多，被掃描的索引範圍就越大。

這帶來兩個缺點：
頁面漂移：插入新銷售時，編號永遠從頭算起，內容會跟著位移。
回應時間隨往回翻的頁數增加。

尋位法#

尋位法避開了這兩個問題，因為它用前一頁的值當分界：搜尋「必須排在前一頁最後一筆之後」的那些值。這可以用一個簡單的 where 子句表達——換個角度說：尋位法根本不去選取已經顯示過的值。

先假設每天只有一筆銷售（SALE_DATE 因而是唯一鍵）。由於是遞減排序，「排在某日期之後」要用小於（<）條件（遞增排序則用 >）：

SELECT *
  FROM sales
 WHERE sale_date < ?
 ORDER BY sale_date DESC
 FETCH FIRST 10 ROWS ONLY;

用前一頁的最後一個值取代列號來指定下界，效能上的收益極大：資料庫能把 sale_date < ? 用於索引存取，也就是真正地跳過前幾頁的列。此外，即使有新列插入，結果也保持穩定。

前提：排序必須具決定性#

但如果一天有多筆銷售，這個做法就失效了：拿第一頁的最後日期（「昨天」）當界線，會把昨天的所有結果都跳過——不只是第一頁已顯示的那些。

問題在於 order by 子句沒有建立具決定性的列順序，而那正是「用簡單範圍條件切頁」的前提。

沒有具決定性的 order by，資料庫依定義就不會交出具決定性的列順序。你平常之所以得到一致的順序，只是因為資料庫通常以相同方式執行查詢。實際上資料庫大可把 SALE_DATE 相同的列打亂，仍然滿足 order by。
在較新的版本中，你甚至可能每次執行都得到不同順序——不是資料庫故意打亂，而是它可能採用平行查詢執行：同一個執行計畫，因為執行緒完成順序不確定，就可能產生不同的列順序。

即使功能規格只要求「依日期排序、最新在前」，身為開發者的我們仍必須確保 order by 產生具決定性的列順序。做法是：

若管線化 order by 所用的索引還有其他欄位，先把它們加進 order by，這樣還能繼續用該索引做管線化 order by。
若仍不具決定性，就加上任何唯一欄位並相應擴充索引。

Row values 語法#

下例把主鍵 SALE_ID 加進 order by 與索引，並對兩個欄位整體套用「排在其後」的邏輯：

CREATE INDEX sl_dtid ON sales (sale_date, sale_id);

SELECT *
  FROM sales
 WHERE (sale_date, sale_id) < (?, ?)
 ORDER BY sale_date DESC, sale_id DESC
 FETCH FIRST 10 ROWS ONLY;

這裡用了鮮為人知的 row values 語法：把多個值組合成一個邏輯單位，套用一般的比較運算子。與純量值一樣，遞減排序時「小於」就等於「排在其後」。

雖然 row values 屬於 SQL 標準，支援的資料庫卻很少：
SQL Server 2012：完全不支援。
Oracle：原則上支援 row values，但不能對它套用範圍運算子（ORA-01796）。
MySQL：能正確求值，但無法在索引存取時把它當作存取述詞。
PostgreSQL：支援語法，且在有對應索引時會用它來存取索引。

延伸：SQL Row Values 的標準定義

除了一般的純量值，SQL 標準也定義了所謂的列值建構子（row value constructor），用來「指定一組有序的值，構成一列或部分列」[SQL:92, §7.1]。語法上就是括號中的列表——最為人熟知的用途是 insert 敘述。

在 where 子句中使用 row value constructor 較少見，卻完全合法。標準確實為它定義了所有比較運算子，例如小於：

「Rx < Ry」為真，若且唯若對所有 i < n 皆有 RXi = RYi，且存在某個 n 使 RXn < RYn。
——SQL:92, §8.2.7.2

其中 i 與 n 代表列表中的位置索引。也就是說：只要某個 RXn 小於對應的 RYn，且所有在它之前的值兩兩相等，則列值 RX 小於 RY。

這個定義讓 RX < RY 等同於「RX 排在 RY 之前」——正是尋位法所需的邏輯。

不支援 row values 時的近似做法#

在不支援 row values 的資料庫上，仍可用近似版本的尋位法——雖然不如 PostgreSQL 的 row values 優雅高效。以 Oracle 為例，改用「一般」比較來表達同樣邏輯：

SELECT *
  FROM ( SELECT *
           FROM sales
          WHERE sale_date <= ?
            AND NOT (sale_date = ? AND sale_id >= ?)
          ORDER BY sale_date DESC, sale_id DESC
       )
 WHERE rownum <= 10;

where 子句由兩部分組成：

只看 SALE_DATE 的 <= 條件——它選出比所需更多的列。這部分夠簡單，所有資料庫都能用它存取索引。
NOT (...)——移除前一頁已顯示的多餘列。

---------------------------------------------------------------
|Id | Operation                       | Name    | Rows | Cost |
---------------------------------------------------------------
| 0 | SELECT STATEMENT                |         |    10 |    4 |
|*1 | COUNT STOPKEY                   |         |       |      |
| 2 |   VIEW                          |         |    10 |    4 |
| 3 |    TABLE ACCESS BY INDEX ROWID  | SALES   | 50218 |    4 |
|*4 |     INDEX RANGE SCAN DESCENDING | SL_DTIT |     2 |    3 |
---------------------------------------------------------------
Predicate Information:
   1 - filter(ROWNUM<=10)
   4 - access("SALE_DATE"<=:SALE_DATE)
       filter("SALE_DATE"<>:SALE_DATE
           OR "SALE_ID"<TO_NUMBER(:SALE_ID))

SALE_DATE 上的存取述詞讓資料庫得以跳過「前幾頁已完整顯示的日子」；第二部分只是篩選述詞——資料庫會再檢視前一頁的少數幾筆，但立刻丟棄。

延伸：等價邏輯的可索引性

同一個邏輯條件永遠有多種寫法。上面的跳過邏輯也可以寫成：

WHERE (
          (sale_date < ?)
        OR
          (sale_date = ? AND sale_id < ?)
      )

這個版本只用包含性條件，對人類而言大概更好懂。但資料庫的觀點不同：它認不出這個 where 子句是在選取「從某組 SALE_DATE/SALE_ID 開始的所有列」，於是把整個 where 子句當成篩選述詞。我們至少會期待最佳化工具能把 SALE_DATE <= ? 從兩個 or 分支中「提取出來」，但沒有任何資料庫提供這項服務。

不過我們可以手動加上這個冗餘條件（雖然可讀性沒有變好）：

WHERE sale_date <= ?
  AND (
           (sale_date < ?)
        OR
           (sale_date = ? AND sale_id < ?)
      )

所幸所有資料庫都能把這部分用作存取述詞。

但這個子句比前面的近似寫法更難掌握；而且原本的寫法還能避免一個風險：那個「看起來不必要」的冗餘部分日後被人誤刪。

兩種方法的比較#

量測精度不足以看出圖左側的差異，但約從第 20 頁開始，兩者的差距就清楚可見：偏移法線性上升，尋位法維持平坦。

尋位法當然也有缺點，最主要的就是不好用：
where 子句必須非常小心地措辭。
無法抓取任意頁。
要改變瀏覽方向，必須把所有比較與排序操作全部反轉。

不過，恰恰是「跳頁」與「往回瀏覽」這兩項功能，在使用者介面採用無限捲動機制時並不需要。