2 Replies Latest reply: Jun 21, 2012 2:24 AM by Purvesh K RSS

    Bulk Insert

    visK
      Hi

      I got a table having records around 10000000. where I insert through Simple Insert statement It takes around 2 minutes and Bulk Insert also takes same time.

      Pl refer the following script

      INSERT INTO servers2 SELECT * FROM servers TAKES AROUND 2 MINUTES

      DECLARE
      CURSOR s_cur IS
      SELECT *
      FROM servers;

      TYPE fetch_array IS TABLE OF s_cur%ROWTYPE;
      s_array fetch_array;
      BEGIN
      OPEN s_cur;
      LOOP
      FETCH s_cur BULK COLLECT INTO s_array LIMIT 1000000;

      FORALL i IN 1..s_array.COUNT
      INSERT INTO servers2 VALUES s_array(i);
      COMMIT;
      EXIT WHEN s_cur%NOTFOUND;
      END LOOP;
      CLOSE s_cur;
      COMMIT;
      END;

      Pl suggest what need to be done to reduce processing time thr BULK INSERT

      Thanks
      Vishal
        • 1. Re: Bulk Insert
          rp0428
          Wrong forum! This question belongs in the SQL and PL/SQL forum.
          PL/SQL

          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.
          • 2. Re: Bulk Insert
            Purvesh K
            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.
            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
            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?

            Since, this is a SQL query, for more information please do use the SQL and PLSQL Category to post similar questions.

            Regards,
            P.

            Edited by: PurveshK on Jun 21, 2012 12:54 PM