| help required in optimizing a query [message #372507] |
Fri, 16 February 2001 08:13  |
sunil menon
Messages: 3
Registered: February 2001
|
Junior Member |
|
|
I need to fetch a set of 10 records sequentially based on ROWID from a table
The tables uw_worksheet contains approximately 100K records. (primary key UWWORKSHEETID)
The table uw_routing contains 100K records (primary_key worksheet_id and a sequence number)
The table pr_user contains 100 records (primary key user_id)
This below given query is giving us the desired output but it is very costly.
Can anyone optimize this query.
Thanks
****************************************************************************
This query gives the 6th to 10th record from the table
select uww.policy_number,uww.last_save_datetime ,
decode(uww.status,1,'Unassigned',2,pr.last_name || ' ' || pr.first_name),
decode(uww.status,1,trunc(uww.uw_in_date),2,trunc(uwr.route_datetime))
from pr_user pr, uw_routing uwr, uw_worksheet uww
where pr.userid=uww.uw_owner_userid
and uww.uwworksheetid=uwr.uwworksheetid(+)
and 10 > ( select count (*) from uw_worksheet uw1
where uw1.rowid > uww.rowid )
minus
select uww.policy_number,uww.last_save_datetime ,
decode(uww.status,1,'Unassigned',2,pr.last_name || ' ' || pr.first_name),
decode(uww.status,1,trunc(uww.uw_in_date),2,trunc(uwr.route_datetime))
from pr_user pr, uw_routing uwr, uw_worksheet uww
where pr.userid=uww.uw_owner_userid
and uww.uwworksheetid=uwr.uwworksheetid(+)
and 5 > ( select count (*) from uw_worksheet uw1
where uw1.rowid > uww.rowid ) order by uww.policy_number
********************************************************************************
|
|
|
|
| Re: help required in optimizing a query [message #372511 is a reply to message #372507] |
Fri, 16 February 2001 12:18  |
Andrew again...
Messages: 270
Registered: July 2000
|
Senior Member |
|
|
Is this for scrolling purposes??
If so, it would usually be better to use a ref-cursor which is just a pointer into the result set. From your application you can then control how the records are retreived. Another way would be to read the restult set into your application and then the scrolling is handled in your application. i.e. only one DB query is executed. Of course you need to think about what to do when you insert or delete a record to refresh the resultset.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
