OraFAQ Forum: SQL & PL/SQL » performance tuning on paging result set

Home » SQL & PL/SQL » SQL & PL/SQL » performance tuning on paging result set

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

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%'
;

Report message to a moderator

Re: performance tuning on paging result set [message #18437 is a reply to message #18435]

Thu, 31 January 2002 01:18

pratap kumar tripathy
Messages: 660
Registered: January 2002

Senior Member

hi,

try this

select MMBR_NUM,MMBR_NAME,
from
(select a.MMBR_NUM,a.MMBR_NAME,ROWNUM rn
from MEMBERSHIP a
where a.mmbr_lang= 'en' and
and upper(a.MMBR_NUM) like '2%'
...put all other condition here......
and ROWNUM <= 40)
where rn > 20 ;

cheers
pratap

Report message to a moderator

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?

Report message to a moderator

Re: performance tuning on paging result set [message #18445 is a reply to message #18435]

Thu, 31 January 2002 04:54

Frank
Messages: 7901
Registered: March 2000

Senior Member

What ?

I wanna retrieve record page (per 20 record each) by page which fulfill my criteria.

or you can treat no criteria at all but just showing all record page by page.

Report message to a moderator

Re: performance tuning on paging result set [message #18447 is a reply to message #18435]

Thu, 31 January 2002 05:58

Frank
Messages: 7901
Registered: March 2000

Senior Member

no. I am writing java program

Report message to a moderator

Re: performance tuning on paging result set [message #18449 is a reply to message #18447]

Thu, 31 January 2002 06:35

Mike
Messages: 417
Registered: September 1998

Senior Member

Why don't you solve the problem in java?
Wouldn't it be easier to use the next() and previous() functions of the class ResultSet to get the next/previous 20 records?

Maybe have also a look at:
http://technet.oracle.com/docs/products/oracle8i/doc_library/817_doc/java.817/a83724/resltse5.htm#1023642

Report message to a moderator

Previous Topic:	REFERENCES and FOREIGN KEY
Next Topic:	I can't have my SQL prompt after excuting my stored procedure

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Mar 13 20:20:18 CDT 2025