This content has been marked as final. Show 2 replies
Wrong forum! This question belongs in the SQL and PL/SQL forum.
1. Open a new thread in the proper forum
2. Edit this thread to add a link to the new thread and directing people to follow up there.
3. Mark this question ANSWERED so people will follow up in the the thread.
Before you post in the SQL and PL/SQL forum read the FAQ there for how to post a tuning reqest and the information that you need to provide. You haven't provided any of the information needed to try to answer your question: 4 digit Oracle version (result of SELECT * FROM V$VERSION), table DDL, the indexes available, number of records in the table, number of records being inserted, whether this is a one-time operation or needs to be performed on a regular basis.
Pl suggest what need to be done to reduce processing time thr BULK INSERT
You should not be using BULK INSERT for this. In general SQL will always be faster than PL/SQL and for your use case since the same basic INSERT is being used in both examples if there is a performance issue it is with the SQL INSERT itself.
Why should you use a Bulk Collect in First Place?
Below case shall explain why should you go with Insert as Select instead of bulk collect.
Insert as Select is approx. 2 seconds faster, consumes less Memory, Less Context switches when compared with PL/SQL Bulk Collect. One major advantage is, it is a Lot more maintainable, compare 1 line code with 20 lines of code?
create table test_table as select * from employees where 1 = 2; insert into test_table select * from employees connect by level <= 3; select count(1) from test_Table; create table test_table_emp as select * from test_table where 1 = 2; set timing on; insert into test_table_emp select * from test_table; /*1236599 rows inserted 10,923ms elapsed*/ set timing on; declare type emp_type is table of test_Table%rowtype index by pls_integer; emp emp_type; cursor c_cur is select * from test_table; begin open c_cur; loop fetch c_cur bulk collect into emp limit 1000; forall i in emp.first..emp.last insert into test_table_emp values emp(i); exit when c_cur%notfound; end loop; end; /* 12,903ms elapsed */ set timing on; insert /*+ APPEND */ into test_table_emp select * from test_table; 1236599 rows inserted 1,430ms elapsed
Since, this is a SQL query, for more information please do use the SQL and PLSQL Category to post similar questions.
Edited by: PurveshK on Jun 21, 2012 12:54 PM