Home » Other » Client Tools » Cursor with rownum - Urgent how to speed up
Cursor with rownum - Urgent how to speed up [message #38672] Mon, 06 May 2002 01:30 Go to next message
Stupid
Messages: 8
Registered: November 2001
Junior Member
Dear all,

I have to query 1000000 records to update.
So, I want to open the cursor for every 1000 rows.
I try to declare
Cursor cur_temp is
select * from emp
where dept = 'ACT' and
rownum between 1000 and 2000;

But I found that if I do not use the rownum condition,
the response time is several seconds. After I add the rownum condition, it take several minutes to respond.
I think the select statement perform full table scan after rownum added.
How can I speed up this query ?? Many Thanks
Re: Cursor with rownum - Urgent how to speed up [message #38680 is a reply to message #38672] Mon, 06 May 2002 04:26 Go to previous messageGo to next message
Rick Cale
Messages: 111
Registered: February 2002
Senior Member
Take a look at bulk binds. I think this is what you
are need. I do not think rownum was intended to be
used for this purpose. Acutally I do not believe Oracle
guarantees it will return the same recordset for the same set of rownum values.
Re: Cursor with rownum - Urgent how to speed up [message #38687 is a reply to message #38672] Mon, 06 May 2002 07:22 Go to previous messageGo to next message
ranjan
Messages: 20
Registered: August 1999
Junior Member
You can have like this:

select *
bulk collect into
variable( should be of table type)
from emp
where dept = 'ACT' LIMIT 1000;
Re: Cursor with rownum - Urgent how to speed up [message #38699 is a reply to message #38672] Tue, 07 May 2002 02:13 Go to previous message
John R
Messages: 156
Registered: March 2000
Senior Member
You cannot use the 'rownum between 1000 and 2000' cunstruct you have in your query.
Rownum simply refers to the number of the row in the record set you have selected.
So, it will check each row, see if it has already select over a 1000 others, find it hasn't, reject the row and move onto the next.

If your update is something that you can easily detect (ie updating a column from null to a value, then you can rewrite the cursor as

Cursor cur_temp is
select * from emp
where dept = 'ACT'
and column_to_update IS null;

Then open this cursor, step through 1000 rows, close the cursor, commit and start again.

From a performance point of view, it may well be quicker to do a single update statement with a rownum<1000 in.
Previous Topic: Imp: View a trigger history
Next Topic: hi - VERY VERY URGENT
Goto Forum:
  


Current Time: Thu Mar 28 17:33:05 CDT 2024