我如何改进SQL分页？

问题描述

我的分页速度很慢。我怎么改进它？谢谢

My paging is so slow. how I improve it? Thanks

SELECT 
T.* 
FROM (
SELECT 
QRY.* 
, ROWNUM RNUM 
FROM (
SELECT				 
A.PRPTY_NO               
, B.PRPTY_NM       
, B.CL_CD                
, B.ORG_PRPTY_REGNO      
, A.MODEL                
, B.PFMNC_MESURE_UNIT    
, B.PFMNC_CPCTY          
, A.MADE_NATION          
, A.MADE_YEAR            
, A.PRPOS_DAY
, B.USE_START_PNT_RECK            
, GET_DSPS_LEDG_QTY(B. PRPTY_NO, B.LEDG_QTY,  '20141231' /**P*/) AS LEDG_QTY                 
, B.REGSTR_AMOUNT                
, B.BVN_PC               
, GET_ACCUM_DEPRECIATION(A.PRPTY_NO,  '20141231' /**P*/,B.BVN_PC,B.DPRC_YEAR, B.USE_START_PNT_RECK, B.DPRC_BSIS_AMT, B.DPRC_DAY, B.SRVIVE_VALU, B.DPRC_ACMTLAMOUNT, B.DPRC_MTHD) AS  DPRC_ACMTLAMOUNT     
, B.SRVIVE_VALU
, B.BVN_PC - GET_ACCUM_DEPRECIATION(A.PRPTY_NO,  '20141231' /**P*/,B.BVN_PC,B.DPRC_YEAR, B.USE_START_PNT_RECK, B.DPRC_BSIS_AMT, B.DPRC_DAY, B.SRVIVE_VALU, B.DPRC_ACMTLAMOUNT, B.DPRC_MTHD) AS SRVIVE_AMT   
, C.ADDR_LCLAS_NM AS AIMAG_NM 
, C.ADDR_MLSFC_NM AS SOM_NM              
, C.ORG_NM AS ORG_NM               
, C.ORG_REG_NO AS REG_NO               
, C.SPC_POS_CD AS OWNER_NO
, B.ADD_FILE_ID	
FROM TB_EQPMN_REGSTR A
LEFT JOIN TB_BASS_REGSTR B
ON A.PRPTY_NO = B.PRPTY_NO
LEFT JOIN TB_ORG C
ON B.MNGTORG_ID = C.ORG_ID		
WHERE 1=1	
AND C.UPPER_ORG_ID IN (SELECT ORG_ID FROM TB_USER_MNGTORG WHERE USER_ID =  'MBIC_ADMIN' /**P*/)
AND B.PROVREG_YN =  'N' /**P*/			
AND (( '20141231' /**P*/ >= TO_CHAR(B.REG_DTM, 'YYYYMMDD') AND  '20141231' /**P*/ >= TO_CHAR(B.UPD_DTM, 'YYYYMMDD') AND B.DEL_YN = 'N')
OR ( '20141231' /**P*/ >= TO_CHAR(B.REG_DTM, 'YYYYMMDD') AND TO_CHAR(B.UPD_DTM, 'YYYYMMDD') >  '20141231' /**P*/))				  
ORDER BY B.REG_DTM DESC
)QRY
)T
WHERE T.RNUM  BETWEEN ( 0 /**P*/+1) AND ( 0 /**P*/+ 10 /**P*/)

我尝试了什么：



我改变了如上

What I have tried:

I changed like on

AND ROWNUM BETWEEN ( 10 /**P*/+1) AND ( 10 /**P*/+ 10 /**P*/)
ORDER BY B.REG_DTM DESC

但在第二页时没有工作

but not working when second page

AND ROWNUM BETWEEN ( 10 /**P*/+1) AND ( 10 /**P*/+ 10 /**P*/)
ORDER BY B.REG_DTM DESC

推荐答案

这篇关于我如何改进SQL分页？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！