I tried this way and infact bulk collect took about 1 minutes more than
my current case?
Any thoughts....
thanks
-----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).
