Pagination question

From: lsllcm <lsllcm_at_gmail.com>
Date: 21 Apr 2006 02:59:56 -0700
Message-ID: <1145613596.500393.103410@u72g2000cwu.googlegroups.com>

Hi All,

I have one question about pagination.

The following are the sql.

select * from (SELECT
B1PERMIT.B1_PER_ID1, B1PERMIT.B1_PER_ID2, B1PERMIT.B1_PER_ID3 FROM B1PERMIT, b3addres
WHERE B1PERMIT.SERV_PROV_CODE = 'NOLA'
AND B1PERMIT.REC_STATUS = 'A'
and B3ADDRES.SERV_PROV_CODE = 'NOLA'

AND  B1PERMIT.SERV_PROV_CODE = B3ADDRES.SERV_PROV_CODE
AND  B1PERMIT.B1_PER_ID1 = B3ADDRES.B1_PER_ID1
AND  B1PERMIT.B1_PER_ID2 = B3ADDRES.B1_PER_ID2
AND  B1PERMIT.B1_PER_ID3 = B3ADDRES.B1_PER_ID3
AND  B1PERMIT.REC_STATUS = B3ADDRES.REC_STATUS

AND B3ADDRES.B1_SITUS_ZIP like '0%'
ORDER BY B1PERMIT.B1_FILE_DD DESC)
where ROWNUM < 100
;

I want to return the first 100 rows.

The oracle version is 9.2.0.7

The following are the 10046 event trace file.

STAT #1 id=1 cnt=99 pid=0 pos=1 obj=0 op='COUNT STOPKEY '
STAT #1 id=2 cnt=99 pid=1 pos=1 obj=0 op='VIEW  '
STAT #1 id=3 cnt=99 pid=2 pos=1 obj=0 op='SORT ORDER BY STOPKEY '
STAT #1 id=4 cnt=44836 pid=3 pos=1 obj=0 op='NESTED LOOPS  '
STAT #1 id=5 cnt=44836 pid=4 pos=1 obj=34861 op='TABLE ACCESS BY INDEX

ROWID B3ADDRES '
STAT #1 id=6 cnt=45326 pid=5 pos=1 obj=34864 op='INDEX RANGE SCAN B3ADDRES_ZIP_IX '
STAT #1 id=7 cnt=44836 pid=4 pos=2 obj=34849 op='TABLE ACCESS BY INDEX ROWID B1PERMIT '
STAT #1 id=8 cnt=44836 pid=7 pos=1 obj=34854 op='INDEX UNIQUE SCAN B1PERMIT_PK ' The oracle retrieve the index B3ADDRES_ZIP_IX based on the where condition, it does not use the index on "order by column" B1PERMIT.B1_FILE_DD. So the rownum usage for pagination in this kind sql is useless.

Can anyone give some suggestion how to tune the sql?

Thanks
Jacky Received on Fri Apr 21 2006 - 04:59:56 CDT