performance tuning on paging result set [message #18435] |
Thu, 31 January 2002 00:55 |
Frank
Messages: 7901 Registered: March 2000
|
Senior Member |
|
|
I have a table MEMBERSHIP which consist of 440000 record and the sql below to provide subset of result by paging function while the sql takes me 22 sec to finish on a SUN E450 with 2G RAM config machine. Is ther any other better method to achieve the same function ?
select MMBR_NUM,
MMBR_NAME,
from ( select a.MMBR_NUM,a.MMBR_NAME,ROWNUM rn
from MEMBERSHIP a
where a.mmbr_lang= 'en'
) where rn > 20
and rn <= 40
and upper(a.MMBR_NUM) like '2%'
;
After several try I have a trick which is to put the rownum upperbound condition inside the subquery as below while it gives me thundering performance on several pages in the front while the performance degrade dramatically when the number goes larger. Pls help
select MMBR_NUM,MMBR_NAME,
from ( select a.MMBR_NUM,a.MMBR_NAME,ROWNUM rn
from MEMBERSHIP a
where a.mmbr_lang= 'en' and ROWNUM <= 40)
where rn > 20 and rn <= 40
and upper(a.MMBR_NUM) like '2%'
;
|
|
|
|
Re: performance tuning on paging result set [message #18439 is a reply to message #18435] |
Thu, 31 January 2002 03:01 |
Mike
Messages: 417 Registered: September 1998
|
Senior Member |
|
|
For what do you need this?
Is there any performance difference without the upper function (which is not needed)?
How many data fit the criteria MMBR_NUM like '2%' and mmbr_lang= 'en' ?
Are there any indexes?
|
|
|
|
|
|