Click here to Skip to main content
15,867,308 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
My paging is so slow. how I improve it? Thanks
SQL
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
SQL
AND ROWNUM BETWEEN ( 10 /**P*/+1) AND ( 10 /**P*/+ 10 /**P*/)
ORDER BY B.REG_DTM DESC

but not working when second page
SQL
AND ROWNUM BETWEEN ( 10 /**P*/+1) AND ( 10 /**P*/+ 10 /**P*/)
ORDER BY B.REG_DTM DESC
Posted
Updated 25-Oct-16 8:45am
v3
Comments
Jörgen Andersson 25-Oct-16 9:06am    
What version is your database?
[no name] 25-Oct-16 22:12pm    
Oracle 11g

1 solution

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)[^]
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900