解決Oracle分頁查詢中排序與效率問題

作者：sznmail 2009-03-04 13:32:28

本文將結合作者近日工作中，在ORACLE數據庫分頁查詢時，遇到一個小問題，為大家講解如何解決Oracle分頁查詢中排序與效率問題。

原始未分頁查詢Sql代碼如下：

select ROWNUM rn, t.id ID, o.name YYB,u.name XM, t.MC from tZDYSX   
 t,tuser u,lborganization o where t.cjr=u.id and u.orgid=o.orgcode and t.gx = 1   
 order by ID

結果如下：
RN    ID      YYB       XM     MC
---------- ----------------- ----------------------
   3    49 某證券總部     管理員測試
   4     96 某證券總部     管理員持有上港10000股以上
   5 102 某證券總部     管理員十年規劃
14   105 某證券總部     管理員開發渠道為上海
11   106 某證券總部     管理員萬科A
12   107 某證券總部     管理員 11
13   108 某證券總部     管理員今天過生日的客戶
   2   109 某證券總部     管理員客戶狀態正常
   6 110 某證券總部     管理員無交易
   7    111 某證券總部     管理員 OA
   8 112 某證券總部     管理員幸運客戶
   9    113 某證券總部     管理員風險型
10   114 某證券總部     管理員 tst
22   115 白沙網上交易   安昌彪安客戶正常
   1 118 某證券總部     管理員 213
18   119 某證券總部     管理員客戶號包含1008
17   120 某證券總部     管理員 aaa
19   123 某證券總部     管理員 ssssssss
20   124 某證券總部     管理員 www
21   126 某證券總部     管理員 123123
15   127 某證券總部     管理員 1212
16   128 某證券總部     管理員 aaaaaa

22 rows selected

最初我使用如下Sql代碼查詢：

 select * from (select ROWNUM rn, t.id ID, o.name YYB,u.name XM, t.MC    
from tZDYSX t,tuser u,lborganization o where t.cjr=u.id and u.orgid=o.orgcode and    
t.gx = 1 order by t.ID )Where rn>10 and rn<=20;

這種方法能成功分頁，結果如下：
RN    ID        YYB       XM      MC
---------- ----------------- -------------------
14   105 某證券總部     管理員開發渠道為上海
11   106 某證券總部     管理員萬科A
12   107 某證券總部     管理員 11
13   108 某證券總部     管理員今天過生日的客戶
18   119 某證券總部     管理員客戶號包含1008
17   120 某證券總部     管理員 aaa
19   123 某證券總部     管理員 ssssssss
20   124 某證券總部     管理員 www
15   127 某證券總部     管理員 1212
16   128 某證券總部     管理員 aaaaaa

10 rows selected

從結果看來，有個問題：此語句Sql代碼
order by CJSJ DESC 被執行，但是是在分后的第11到20條記錄的結果集中再進行排序，而不是先排序后分頁。（本來希望顯示ID為112到126，結果變為105到128）

后來變為以下Sql代碼查詢：

SELECT *    
FROM(    
  SELECT ROWNUM RN,TA.*    
  FROM(   
       select t.id ID, o.name YYB,u.name XM, t.MC   
       from tZDYSX t,tuser u,lborganization o    
       where t.cjr=u.id and u.orgid=o.orgcode and t.gx = 1 order by t.ID   
  )TA WHERE ROWNUM <= 20   
)WHERE RN > 10  
SELECT * 
FROM( 
  SELECT ROWNUM RN,TA.* 
  FROM(
       select t.id ID, o.name YYB,u.name XM, t.MC
       from tZDYSX t,tuser u,lborganization o 
       where t.cjr=u.id and u.orgid=o.orgcode and t.gx = 1 order by t.ID
  )TA WHERE ROWNUM <= 20
)WHERE RN > 10

結果如下：
RN    ID     YYB           XM     MC
---------- ----------------- -------------------
11   112 某證券總部     管理員幸運客戶
12   113 某證券總部     管理員風險型
13   114 某證券總部     管理員 tst
14   115 白沙網上交易   安昌彪安客戶正常
15   118 某證券總部     管理員 213
16   119 某證券總部     管理員客戶號包含1008
17   120 某證券總部     管理員 aaa
18   123 某證券總部     管理員 ssssssss
19   124 某證券總部     管理員 www
20   126 某證券總部     管理員 123123

10 rows selected

看來結果是正確的。

總結：第二種方法其中最內層的查詢Sql代碼

select t.id ID, o.name YYB,u.name XM, t.MC   
       from tZDYSX t,tuser u,lborganization o    
       where t.cjr=u.id and u.orgid=o.orgcode and t.gx = 1 order by t.ID

表示不進行翻頁的原始查詢語句。ROWNUM <= 20和RN > 10控制分頁查詢的每頁的范圍。
第二種方法在大多數情況擁有較高的效率。分頁的目的就是控制輸出結果集大小，在上面的分頁查詢語句中，這種考慮主要體現在WHERE ROWNUM <= 20這句上。

選擇第11到20條記錄存在兩種方法，第二種方法正是在查詢的第二層通過ROWNUM <= 20來控制最大值，在查詢的最外層控制最小值。而第一種方法是去掉查詢第二層的WHERE ROWNUM <= 20語句，在查詢的最外層控制分頁的最小值和最大值。

一般來說，第二個查詢的效率比第一個高得多，這是由于CBO 優化模式下，Oracle可以將外層的查詢條件推到內層查詢中，以提高內層查詢的執行效率。對于第二個查詢語句，第2層的查詢條件WHERE ROWNUM <= 20就可以被Oracle推入到內層查詢中，這Oracle查詢的結果一旦超過了ROWNUM限制條件，就終止查詢將結果返回了。

而第一個查詢語句，由于查詢條件Where rn>10 and rn<=20是存在于查詢的第三層，而Oracle無法將第三層的查詢條件推到最內層（即使推到最內層也沒有意義，因為最內層查詢不知道RN代表什么）。因此，對于第一個查詢語句，Oracle最內層返回給中間層的是所有滿足條件的數據，而中間層返回給最外層的也是所有數據。數據的過濾在最外層完成，顯然這個效率要比第二個查詢低得多。

這種分頁對于單表查詢、多表查詢一樣有效。

【編輯推薦】

云數據庫技術漸熱各廠商上演多方斗法
甲骨文推出Oracle托管型CRM R16新功能
Oracle數據庫中段管理的四個技巧

責任編輯：彭凡來源： javaeye

排序 SQL Oracle