高效分頁的SQL技巧(以Oracle為例)

一般的分頁寫法大概就是下面的樣子所謂的“三層嵌套”寫法），

 

  
SELECT *  
  
FROM   (SELECT a.*  
  
              ,rownum rn  
  
        FROM   (SELECT * FROM table_name) a  
  
        WHERE  rownum <= 40)  
  
WHERE  rn >= 21 
 

在遇到顯示資料表中1百萬行以後的記錄時，可能會出現效能問題。有人提出的解決方案是利用索引，首先通過掃描索引查出rowid，然後再通過rowid查出所需記錄。這樣做的問題是如果沒有索引怎麼辦？以Oracle為例，下面是一個更具有一般意義的高效寫法：

 

  
SELECT *  
  
FROM   <your table name> t  
  
WHERE  ROWID IN (SELECT rwd  
  
                 FROM   (SELECT t.rowid rwd  
  
                               ,rownum  n  
  
                         FROM   <your table name> t  
  
                         WHERE  rownum <= 1000000 + 10)  
  
                 WHERE  n >= 1000000) 
 

最佳化的思想是跳到第1百萬位置時只是掃描了rowid，避免了掃描全部欄位。當欄位個數較多時，速度會有十倍的提升。本例在普通的裝置上運行不到1秒就出來結果了。這一方法在千萬級或億級仍然存在問題，其原因在於迴圈次數並未減少。如果真有rwd這樣一個欄位，那麼通過索引和分區等手段，也能夠確保速度。不過，話又說回來，分頁顯示成百上千萬條記錄這樣的事情，其欲達到的業務目的本身可否有更好的解決之道呢？

本文出自 “iData” 部落格，請務必保留此出處http://idata.blog.51cto.com/4581576/1121453