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

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

　　select ROWNUM rn tid ID oname YYBuname XM tMC from tZDYSX  
ttuser ulborganization o where tcjr=uid and id=code and tgx =   
order by ID 

　　結果如下
RN    ID      YYB       XM     MC

       某證券總部     管理員 測試
     某證券總部     管理員 持有上港股以上
    某證券總部     管理員 十年規劃
   某證券總部     管理員 開發渠道為上海
   某證券總部     管理員 萬科A
   某證券總部     管理員
   某證券總部     管理員 今天過生日的客戶
     某證券總部     管理員 客戶狀態正常
    某證券總部     管理員 無交易
   某證券總部     管理員 OA
    某證券總部     管理員 幸運客戶
   某證券總部     管理員 風險型
   某證券總部     管理員 tst
   白沙網上交易   安昌彪 安客戶正常
    某證券總部     管理員
   某證券總部     管理員 客戶號包含
   某證券總部     管理員 aaa
   某證券總部     管理員 ssssssss
   某證券總部     管理員 www
   某證券總部     管理員
   某證券總部     管理員
   某證券總部     管理員 aaaaaa

　　 rows selected

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

　　select * from (select ROWNUM rn tid ID oname YYBuname XM tMC   
from tZDYSX ttuser ulborganization o where tcjr=uid and id=code and   
tgx = order by tID )Where rn> and rn<=; 

　　這種方法能成功分頁結果如下
RN    ID        YYB       XM      MC

   某證券總部     管理員 開發渠道為上海
   某證券總部     管理員 萬科A
   某證券總部     管理員
   某證券總部     管理員 今天過生日的客戶
   某證券總部     管理員 客戶號包含
   某證券總部     管理員 aaa
   某證券總部     管理員 ssssssss
   某證券總部     管理員 www
   某證券總部     管理員
   某證券總部     管理員 aaaaaa

　　 rows selected

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

　　後來變為以下Sql代碼查詢 

　　SELECT *   
FROM(   
SELECT ROWNUM RNTA*   
FROM(  
select tid ID oname YYBuname XM tMC  
from tZDYSX ttuser ulborganization o   
where tcjr=uid and id=code and tgx = order by tID  
)TA WHERE ROWNUM <=   
)WHERE RN >  
SELECT *
FROM(
SELECT ROWNUM RNTA*
FROM(
select tid ID oname YYBuname XM tMC
from tZDYSX ttuser ulborganization o
where tcjr=uid and id=code and tgx = order by tID
)TA WHERE ROWNUM <=
)WHERE RN >

　　結果如下
RN    ID     YYB           XM     MC

   某證券總部     管理員 幸運客戶
   某證券總部     管理員 風險型
   某證券總部     管理員 tst
   白沙網上交易   安昌彪 安客戶正常
   某證券總部     管理員
   某證券總部     管理員 客戶號包含
   某證券總部     管理員 aaa
   某證券總部     管理員 ssssssss
   某證券總部     管理員 www
   某證券總部     管理員

　　 rows selected

　　看來結果是正確的

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

　　

　　select tid ID oname YYBuname XM tMC  
from tZDYSX ttuser ulborganization o   
where tcjr=uid and id=code and tgx = order by tID 

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

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

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

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

　　這種分頁對於單表查詢多表查詢一樣有效

From:http://tw.wingwit.com/Article/program/Oracle/201311/17706.html