我如何改进SQL分页？ [英] How I improve SQL paging?

问题描述

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

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

推荐答案

有一件事是确保您有足够的索引。例如，检查是否存在以下索引。

- B_EQPMN_REGSTR：PRPTY_NO

- TB_BASS_REGSTR：PRPTY_NO，MNGTORG_ID，PROVREG_YN，REG_DTM

- TB_ORG： ORG_ID，UPPER_ORG_ID

- TB_USER_MNGTORG：USER_ID



有点奇怪的是，您的订购是针对外部连接的表列完成的。所以你可能在列表中有NULL值，但我知道你知道这个。



减速的一件事是日期之间的比较。为什么要将日期更改为字符？而是使用条件的日期变量，并将日期值设为日期。



如果您使用的是Oracle 12我建议使用限制条款，请查看在 ORACLE-BASE - 前N个查询中的行限制条款Oracle Database 12c第1版（12.1） [ ^ ]

One thing would be to ensure that you have sufficient indexing in place. For example check the existence of following indexes.
- B_EQPMN_REGSTR: PRPTY_NO
- TB_BASS_REGSTR: PRPTY_NO, MNGTORG_ID, PROVREG_YN, REG_DTM
- TB_ORG: ORG_ID, UPPER_ORG_ID
- TB_USER_MNGTORG: USER_ID

What seems a bit odd is that your ordering is done for a table column which is outer joined. So you may have NULL values in the list, but I take it you're aware of this.

One thing that slows down is the comparison between dates. Why do you change the dates to characters? Instead use a date variable for the condition and let the date values be dates.

If you're using Oracle 12 I'd suggest using limiting clause, have a look at ORACLE-BASE - Row Limiting Clause for Top-N Queries in Oracle Database 12c Release 1 (12.1)[^]

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