-----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: <[EMAIL PROTECTED] INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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: <[EMAIL PROTECTED] INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).