Oracle-L: RE: slow insert

From: Basavaraja, Ravindra <Ravindra.Basavaraja_at_T-Mobile.com>
Date: Mon, 24 Feb 2003 18:08:41 -0800
Message-ID: <F001.00557DDF.20030224180841@fatcity.com>

-----Original Message-----

Sent: Saturday, February 22, 2003 7:54 AM To: Multiple recipients of list ORACLE-L

You can read more abt it in PL/SQL User's Guide and Reference - Chapter 4 - Collections and Records !

anyway, below is the simple code to copy data from emp table. note that, after u fetch from the cursor, u have to check for the notfound condition at the end. say, the emp table has 15 rows and u r fetching 2 rows at a time. during the 1st 7 fetches u will be ok. but during the 8th fetch, the fetch will bring only 1 row and the condition "notfound" will be true. thus u will never process the last set of rows. hence, check for the condition after u have inserted/updated/deleted the rows.

finally, try playing with the limit clause in the fetch and see what number is best for u. considering my hardware etc, i got better performance with 5000 fetches at a time.

also, till 8.1.7. u could not have any stmts in the FORALL loop. only 1, i.e. insert/update/delete. but with 9i u can have a pl/sql block & any dynamic stmts too. moreover, with 9i u can also trap for individual exception whereas with 8.1.7. if there was an error, entire bulk process would rollback.

hope this helps & let me know if u need any more help.

code follows declare cursor c_emp is select empno, ename, hiredate from emp ;

type empno_arr_type is table of number(4); v_empno_arr empno_arr_type;

type name_arr_type is table of varchar2(10); v_ename_arr name_arr_type;

type date_arr_type is table of date;
v_hiredate_arr date_arr_type;

  v_arr_idx           binary_integer := 0;
  v_arr_cnt           binary_integer := 0;

begin
  open c_emp;
  loop
    fetch c_emp bulk collect

     into v_empno_arr, v_ename_arr, v_hiredate_arr
     limit 2;

DO NOT CHECK for notfound here

v_arr_cnt := v_empno_arr.count();
dbms_output.put_line(v_arr_cnt);

forall v_arr_idx in 1 .. v_arr_cnt
insert into emp_copy(empno, ename, hiredate)

values(v_empno_arr(v_arr_idx),v_ename_arr(v_arr_idx),v_hiredate_arr(v_arr_id x));

commit;

note : the condition has to be checked here ONLY exit when c_emp%notfound;

end loop;
close c_emp;

end;
/

-----Original Message-----

Ravindra
Sent: Friday, February 21, 2003 7:09 PM
To: Multiple recipients of list ORACLE-L

Yes I am usng 8.1.7 EE.How do I use bulk inserts..Any docs or links on metalink plss?

-----Original Message-----

Sent: Friday, February 21, 2003 3:34 PM
To: Multiple recipients of list ORACLE-L

if u r having oracle 8.1.7 EE or higher, try using bulk inserts ! that surely will speed up !

and of course, u surely might have thought of APPEND hint & dropping/disabling indexes etc.

-----Original Message-----

Ravindra
Sent: Friday, February 21, 2003 3:08 PM
To: Multiple recipients of list ORACLE-L

hi,

I have an insert statement that will insert about 400000 records into a table having 43million records.The values for the insert statement are from a select statement that has a join.This query take about 5-10minutes.What are the ways in
which we can speed up this process.the statement looks like

insert into .......
select ....... from a,b
where a.col1=b.col1 --->index columns
and a.col2=x -->non index
and b.col2=x -->non index

Thanks
--

--

Please see the official ORACLE-L FAQ: http://www.orafaq.net
--

Author: <netmadcap_at_netzero.com
INET: netmadcap_at_netzero.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services


---------------------------------------------------------------------

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

Please see the official ORACLE-L FAQ: http://www.orafaq.net
--

Author: Basavaraja, Ravindra
INET: Ravindra.Basavaraja_at_T-Mobile.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services


---------------------------------------------------------------------

To REMOVE yourself from this mailing list, send an E-Mail message

--

Please see the official ORACLE-L FAQ: http://www.orafaq.net
--

Author: <netmadcap_at_netzero.com
INET: netmadcap_at_netzero.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services


---------------------------------------------------------------------

To REMOVE yourself from this mailing list, send an E-Mail message

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services


---------------------------------------------------------------------

To REMOVE yourself from this mailing list, send an E-Mail message