RE: frequent commit, example ??? HELP

From: Jacques Kilchoer <Jacques.Kilchoer_at_quest.com>
Date: Thu, 15 Nov 2001 15:41:34 -0800
Message-ID: <F001.003C6E4A.20011115152114@fatcity.com>

> -----Original Message-----
> From: Andrea Oracle [mailto:andreaoracle_at_yahoo.com]
>
> 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 ;
/
  Received on Thu Nov 15 2001 - 17:41:34 CST