Re: Q: sequence numbering speed up

A copy of this was sent to "MGyörgy" <mgyuri_at_fremail.c3.hu> (if that email address didn't require changing) On Wed, 22 Sep 1999 18:38:45 +0200, you wrote:

>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.)

update haztartas set hazt_id = rownum;

will sequentially number the rows in some order (the order the update physically hits the rows)... or better yet:

create sequence my_seq;
update haztartas set hazt_id = my_seq.nextval;

and then use a trigger to maintain this value for new rows as they are added....

Make sure you have lots of REDO log allocated (look for the 'checkpoint not complete' message in your alert*.log... If you see that -- add more log (to avoid excessive checkpointing during the update.)

>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
>

--
See http://govt.us.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'... Current article is "Part I of V, Autonomous Transactions" updated June 21'st  

Thomas Kyte                   tkyte_at_us.oracle.com
Oracle Service Industries     Reston, VA   USA

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Wed Sep 22 1999 - 12:54:02 CDT