> -----Original Message-----
> From: Andrea Oracle [mailto:[EMAIL PROTECTED]]
>
> Thank you all those for responding to the frequent
> commit question. So members suggested using count,
> and loop. May I have real example. (OK, I'm bad at
> pl/sql). Site table has 2 million rows, how to so a
> commit, let's say 5000 rows. Site_id is unique in
> site table. How does the counter fit in the following
> update sql?
>
> update site a set a.site_code =
> (select c.area_code
> from site_location b,
> area c where a.site_id = b.site_id and
> c.area_id = b.area_id);
>
> I put a counter is a sample code, and update runs 10
> times! then commit, then runs another 10 times! then
> commit .... I must miss something. Please give me as
> mush detail as you can. Thank you so much!
In addition to the other examples given with PL/SQL loops, you might try this approach. You say that site_id is unique. If site_id is indexed, and if the site_ids are more or less sequential with little or no gaps, you could do something like this:
declare
site_id_start number ;
site_id_end number ;
commit_count constant number := 1000 ;
begin
select min (site_id), max (site_id)
into site_id_start, site_id_end from site ;
loop
update site a
set a.site_code = (select ....)
where site_id between (site_id_start and site_id_start + commit_count) ;
commit ;
site_id_start := site_id_start + commit_count + 1 ;
exit when site_id_start > site_id_end ;
end loop ;
end ;
/
