Q: sequence numbering speed up

From: MGyörgy <mgyuri_at_fremail.c3.hu>
Date: Wed, 22 Sep 1999 18:38:45 +0200
Message-ID: <7sb0e4$414@helka.iif.hu>

Hi there !
I have a stored procedure, wich is numbering rows form 1 to 4,5 million step by 1-> this could be an unique id:

procedure tmp_hazt is
 cursor c is select 1 from haztartas for update of hazt_id;  i number:=1;
begin
 for r in c loop
   update haztartas set hazt_id=i where current of c;     i:=i+1;
 end loop;
 commit;
end;

and this is REALLY SLOW, I killed it more than one hour later. (NT, Alpha, O7.3.3.)
Some other informatoin:

NAME                 VALUE
-------------------- ----------
Fixed Size                34164
Variable Size          16527832
Database Buffers        6553600
Redo Buffers             163840

How can I speed up this procedure ? The haztartas table is in one big extent , there is no other connecttion, and the hazt_id is not indexed.

thanx in advance:

Mgyuri Received on Wed Sep 22 1999 - 11:38:45 CDT