Bulk insert or cursor processing

From: CC Harvest <ccharvest_at_yahoo.com>
Date: Sun, 25 Mar 2001 19:16:14 -0800
Message-ID: <F001.002D779F.20010325184027@fatcity.com>

Thanks all for the answers for "which is faster" question.
We are a software company, basically we don't know how large the customer's database is, and how they set their rollback segments(though we do have some recommendations), so we usually to force a commit in the application code(Java) after each 50000 records.

Then for the following commands:
insert into tableb select * from tablea; we have to write a stored procedure, either use a cursor:
cursor cur_a as select * from tablea;
count int :=0;
for rec in cur_a
LOOP
insert into tableb values( rec.fields);
count :=count + 1;
if count = 50000 then

   commit;
   count :=0;
end if;
end loop;
commit;

or we can create a temp table temptable(tablea.fields, rownum alias);
then use bulk insert all the time:

select count(*) into v_count from tablea where ...; count :=0;
while (v_count > 0)
loop

   insert into tablea
    select * from temptable
    where rows between count*50000 and
(count+1)*50000-1;

   count :=count + 1;
   v_count := v_count -50000;
   if v_count < 0 then

      v_count :=0;
end loop;

So my question is :
which is faster, if I use bulk insert or curosr processing?

Thanks in advance. And sorry for the long message.

Chris  

---

Do You Yahoo!?
Get email at your own domain with Yahoo! Mail. http://personal.mail.yahoo.com/

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: CC Harvest
  INET: ccharvest_at_yahoo.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L


(or the name of mailing list you want to be removed from).  You may

also send the HELP command for other information (like subscribing).

Received on Sun Mar 25 2001 - 21:16:14 CST