| Cursor with rownum - Urgent how to speed up [message #38672] |
Mon, 06 May 2002 01:30  |
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 #38699 is a reply to message #38672] |
Tue, 07 May 2002 02:13  |
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.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
