In article <8c4dvu$vl6$1_at_nnrp1.deja.com>,
amerar_at_unsu.com wrote:
>
>
> Hi there,
>
> I have a cursor FOR loop that will update over 1 million records. The
cursor
> is defined with the FOR UPDATE clause. I have a counter that
commits after
> every 10000, however when running the program I got this error:
>
> ORA-01002: Fetch out of sequence
>
> What does this mean? Can't I commit within a cursor FOR loop?
>
In general -- you can commit in a cursor for loop (its a bad practice
and can lead to other errors - in particular ORA-1555 if you are
updating the table you are selecting from).
In particular though -- a commit closes all FOR UPDATE queries though.
A commit releases the locks you have aquired. A select for update gets
lots of locks. They are released when you commit -- hence the query
must close. So, you cannot commit in a cursor for loop that has a for
update.
Selecting for update 1,000,000 rows is *extremely* expensive. The
select for update will take a long time to open. Consider locking the
table or just locking the rows you will actually process in the update.
For example, lets say you are going to do 1million out of 5million
records. There is a "needs_processing" field in the record you set to
Y before processing this record. After processing it is set to NULL.
You might use logic such as:
ops$tkyte_at_8i> REM drop table t;
ops$tkyte_at_8i> REM create table t as select a.*, 'Y' needs_processing
from all_objects a;
ops$tkyte_at_8i> REM create index t_idx on t(needs_processing);
ops$tkyte_at_8i> set serveroutput on
ops$tkyte_at_8i> declare
2 cursor c1 is select *
3 from t
4 where needs_processing = 'Y'
5 and rownum < 1000
6 FOR UPDATE;
7
8 keep_going boolean default false;
9 cnt number default 0;
10 begin
11
12
13 loop
14 keep_going := false;
15
16 for x in c1 loop
17 update t set needs_processing = null where current of
c1;
18 cnt := cnt + sql%rowcount;
19 keep_going := true;
20 end loop;
21 commit;
22 dbms_output.put_line( 'processing ' || cnt || '
records...' );
23
24 exit when not keep_going;
25 end loop;
26 end;
27 /
processing 999 records...
processing 1998 records...
processing 2997 records...
processing 3996 records...
processing 4995 records...
processing 5994 records...
processing 6993 records...
processing 7992 records...
processing 8991 records...
processing 9990 records...
processing 10989 records...
processing 11988 records...
processing 12987 records...
processing 13986 records...
processing 14985 records...
processing 15984 records...
processing 16983 records...
processing 17982 records...
processing 18981 records...
processing 19980 records...
processing 20421 records...
processing 20421 records...
PL/SQL procedure successfully completed.
ops$tkyte_at_8i>
ops$tkyte_at_8i> select distinct needs_processing from t;
N
-
The reason I used NULL and Y for needs processing is so the INDEX on T
would only have entries for those rows that needed processing. the
query:
SELECT *
FROM
T WHERE NEEDS_PROCESSING = 'Y' AND ROWNUM < 1000 FOR UPDATE
call count cpu elapsed disk query
current rows
------- ------ -------- ---------- ---------- ---------- ---------- --
Parse 1 0.00 0.00 0 0
0 0
Execute 22 0.00 0.00 0 779
20819 0
Fetch 20443 0.00 0.00 0 41290
0 20421
------- ------ -------- ---------- ---------- ---------- ---------- --
total 20466 0.00 0.00 0 42069
20819 20421
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 31780 (recursive depth: 1)
Rows Row Source Operation
------- ---------------------------------------------------
20421 FOR UPDATE
40885 COUNT STOPKEY
40842 TABLE ACCESS BY INDEX ROWID T
40845 INDEX RANGE SCAN (object id 78037)
will scan the index to get my first 1000 records. After I update them -
- they will not appear in that index any more (since an all NULL index
entry is not made for BTree indices).
> Thanks,
>
> Arthur
> amerar_at_unsu.com
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.
>
--
Thomas Kyte tkyte_at_us.oracle.com
Oracle Service Industries
http://osi.oracle.com/~tkyte/index.html
--
Opinions are mine and do not necessarily reflect those of Oracle Corp
Sent via Deja.com http://www.deja.com/
Before you buy.
Received on Sat Apr 01 2000 - 07:23:25 CST
