Hi,
I have a production tablespace with millions of rows.
I need to set sequence values in each row but I can not lock the table
for any more than 5 seconds.
I created SEQUENCE named tempseq and found that I can update 1000 rows
in a short enough time.

Here is the script I tested. I have tested it but I don't understand
how each row (from the SELECT) is getting processed only once, or if
this script even guarantees that this will happen.

If someone can explain how ROWNUM is working in this UPDATE then that
would help me.
Alternative solutions (that don't involve explicit cursors) are also
welcome.

begin
loop
update (select seq_no from mytable) set seq_no = tempseq.nextval
where rownum <= 1000;
exit when SQL%FOUND = false;
commit;
end loop;
end;


TIA
-Darren

On 26 jun, 12:54, darr...@yahoo.com wrote:
> Hi,
> I have a production tablespace with millions of rows.
> I need to set sequence values in each row but I can not lock the table
> for any more than 5 seconds.
> I created SEQUENCE named tempseq and found that I can update 1000 rows
> in a short enough time.
>
> Here is the script I tested. I have tested it but I don't understand
> how each row (from the SELECT) is getting processed only once, or if
> this script even guarantees that this will happen.
>
> If someone can explain how ROWNUM is working in this UPDATE then that
> would help me.
> Alternative solutions (that don't involve explicit cursors) are also
> welcome.
>
> begin
> loop
> update (select seq_no from mytable) set seq_no = tempseq.nextval
> where rownum <= 1000;
> exit when SQL%FOUND = false;
> commit;
> end loop;
> end;
>
> TIA
> -Darren

You need to include some condition to assure the update affects "un-
updated" rows only.

as in

update (etc)
set seq_no = (etc) where rownum <= 1000
and seq_no is null;

This way the next update will affect the next 1000 rows and so on.

But make sure to look up "bulk collect" and "forall" updating
examples, which D. Morgan of this same newsgroup recently pointed me
to! Much higher performance.

> But make sure to look up "bulkcollect" and "forall" updating
> examples, which D. Morgan of this same newsgroup recently pointed me
> to! Much higher performance.

TYVM. Useful information for more that just this case!

This post has an example of bulk collect from a sequence (through a
dummy table):
http://groups.google.com/group/comp.... fec1f853a5501
Is this a reasonably efficient method? I probably want to bulk collect
about 1000 values at a time.

On 26 jun, 14:56, darr...@yahoo.com wrote:
> > But make sure to look up "bulkcollect" and "forall" updating
> > examples, which D. Morgan of this same newsgroup recently pointed me
> > to! Much higher performance.
>
> TYVM. Useful information for more that just this case!
>
> This post has an example of bulk collect from a sequence (through a
> dummy table):http://groups.google.com/group/comp....rver/browse_th...
> Is this a reasonably efficient method? I probably want to bulk collect
> about 1000 values at a time.

You don't need to bulk collect from the sequence. I suggest you bulk
collect the primary key of the table being updated.

Then, using FORALL, you update the records with the sequence's
nextval.

As follows (example modified from D. Morgan's page)

CREATE TABLE parent (part_num NUMBER, part_name VARCHAR2(10),seq_no
number);
ALTER TABLE parent ADD PRIMARY KEY (part_num,part_name);

INSERT INTO parent (part_num,part_name)
SELECT 1,'a' FROM dual UNION
SELECT 2,'b' FROM dual UNION
SELECT 3,'c' FROM dual UNION
SELECT 4,'d' FROM dual ;

declare
TYPE array_one IS TABLE OF parent.part_num%TYPE;
TYPE array_two IS TABLE OF parent.part_name%TYPE;
tbl_num array_one;
tbl_name array_two;

CURSOR r IS
SELECT part_num,part_name
FROM parent;

BEGIN
OPEN r;
LOOP
FETCH r BULK COLLECT INTO tbl_num,tbl_name LIMIT 1000;

FORALL i IN 1..tbl_num.COUNT
UPDATE parent SET seq_no=tempseq.NEXTVAL
WHERE part_num=tbl_num(i)
AND part_name=tbl_name(i);

EXIT WHEN r%NOTFOUND;
END LOOP;
COMMIT;
CLOSE r;
END ;
/

(remember, I didn't know about bulk collect three days ago, so you
might want to look it up some more in the manual)

Bulk collect would be usefull if we are upding records one by one
through a loop
where we spent time in switch over from sql eng. to plsql. eng. But
here we can do this
directly through one update.

Let see if someone else can put more info, how bulk collect would be
usefull.


Regards,
Jagjeet Singh.

On 26 jun, 17:11, Jagjeet Singh <jagjeet.ma...@gmail.com> wrote:
> Bulk collect would be usefull if we are upding records one by one
> through a loop
> where we spent time in switch over from sql eng. to plsql. eng. But
> here we can do this
> directly through one update.
>
> Let see if someone else can put more info, how bulk collect would be
> usefull.
>
> Regards,
> Jagjeet Singh.

According to the original poster, using only one update would lock the
table for too long. He determined that updating 1000 rows at a time
resulted in a short-enough lock.

begin
loop
update table set col1 = sequence.nextval where col1 is null and rownum
< 1001;
commit;
end loop;
end;

On Jun 26, 2:57 pm, "Chris L." <diver...@uol.com.ar> wrote:
> You don't need to bulk collect from the sequence. I suggest you bulk
> collect the primary key of the table being updated.
>
> Then, using FORALL, you update the records with the sequence's
> nextval.
>
> As follows (example modified from D. Morgan's page)
>
> CREATE TABLE parent (part_num NUMBER, part_name VARCHAR2(10),seq_no
> number);
> ALTER TABLE parent ADD PRIMARY KEY (part_num,part_name);
>
> INSERT INTO parent (part_num,part_name)
> SELECT 1,'a' FROM dual UNION
> SELECT 2,'b' FROM dual UNION
> SELECT 3,'c' FROM dual UNION
> SELECT 4,'d' FROM dual ;
>
> declare
> TYPE array_one IS TABLE OF parent.part_num%TYPE;
> TYPE array_two IS TABLE OF parent.part_name%TYPE;
> tbl_num array_one;
> tbl_name array_two;
>
> CURSOR r IS
> SELECT part_num,part_name
> FROM parent;
>
> BEGIN
> OPEN r;
> LOOP
> FETCH r BULK COLLECT INTO tbl_num,tbl_name LIMIT 1000;
>
> FORALL i IN 1..tbl_num.COUNT
> UPDATE parent SET seq_no=tempseq.NEXTVAL
> WHERE part_num=tbl_num(i)
> AND part_name=tbl_name(i);
>
> EXIT WHEN r%NOTFOUND;
> END LOOP;
> COMMIT;
> CLOSE r;
> END ;
> /

This works great. Much faster than my original script.
Thanks.

> (remember, I didn't know about bulk collect three days ago, so you
> might want to look it up some more in the manual)
This example is as good as any from the manual.

Jagjeet Singh wrote:
> begin
> loop
> update table set col1 = sequence.nextval where col1 is null and rownum
> < 1001;
> commit;
> end loop;
> end;
>

I don't know what this is other than one of the worst examples of PL/SQL
I have seen in a very long time. And I teach students new to PL/SQL.
--
Daniel A. Morgan
University of Washington
damorgan@x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

> I don't know what this is other than one of the worst examples of PL/SQL
> I have seen in a very long time. And I teach students new to PL/SQL.

what other way you can suggest ?