bulk insert - to make it faster

From: picksdba <Utpal.Dhar_at_gmail.com>
Date: 27 Mar 2006 10:04:30 -0800
Message-ID: <1143482670.025489.188460@e56g2000cwe.googlegroups.com>

I have to insert 32 million records and i have pasted the script below that i am using. It is taking a considerable amount of time and wanted to know if there was a way to make it faster.

Thanks

DECLARE TYPE src_history_date IS TABLE OF
rb_hour_price_history.history_date%TYPE INDEX BY BINARY_INTEGER; TYPE src_ppdid IS TABLE OF sys_price_product.id%TYPE

     INDEX BY BINARY_INTEGER;
TYPE src_mkt_date IS TABLE of sys_price_archive.interval%TYPE

     INDEX BY BINARY_INTEGER;
TYPE src_mkt_price IS TABLE of rb_hour_price_history.mkt_price%TYPE

     INDEX BY BINARY_INTEGER;
TYPE src_price_adj1 IS TABLE of sys_price_archive.price_adj1%TYPE

     INDEX BY BINARY_INTEGER;
TYPE src_price_adj2 IS TABLE of sys_price_archive.price_adj2%TYPE

     INDEX BY BINARY_INTEGER;
TYPE src_price_adj3 IS TABLE of sys_price_archive.price_adj3%TYPE

     INDEX BY BINARY_INTEGER;
TYPE src_price_adj4 IS TABLE of sys_price_archive.price_adj4%TYPE

     INDEX BY BINARY_INTEGER;
TYPE src_was_processed IS TABLE of sys_price_archive.was_processed%TYPE

     INDEX BY BINARY_INTEGER;
TYPE src_create_date IS TABLE of rb_hour_price_history.create_date%TYPE

     INDEX BY BINARY_INTEGER;
TYPE src_modify_date IS TABLE of rb_hour_price_history.modify_date%TYPE

     INDEX BY BINARY_INTEGER;
TYPE src_created_by IS TABLE of sys_price_archive.created_by%TYPE

     INDEX BY BINARY_INTEGER;
TYPE src_modified_by IS TABLE of sys_price_archive.modified_by%TYPE

     INDEX BY BINARY_INTEGER;

src_history_date_array src_history_date      ;
src_ppdid_array src_ppdid                    ;
src_mkt_date_array src_mkt_date              ;
src_mkt_price_array src_mkt_price            ;
src_price_ajd1_array src_price_adj1          ;
src_price_ajd2_array src_price_adj2          ;
src_price_ajd3_array src_price_adj3          ;
src_price_ajd4_array src_price_adj4          ;
src_was_processed_array src_was_processed    ;
src_create_date_array src_create_date        ;
src_modify_date_array src_modify_date        ;
src_created_by_array src_created_by          ;
src_modified_by_array src_modified_by        ;

l_import_until_date date := '28-FEB-06'; j number := 1;

CURSOR c1 IS
SELECT fp_code, ppd_id
  FROM external_hyrly_fp_map;

CURSOR c2 (fp varchar2, ppdid number, iud date) is SELECT rdp.history_date,

       ppdid,
       to_date(to_CHAR(TO_DATE(TO_CHAR(mkt_date ||'

'||decode(length(mkt_time),3,lpad(mkt_time,4,0),DECODE(mkt_time,'2400',NULL,mkt_time))||'00'),'DD-MON-RR HH24MISS'),'DD-MON-RR HH24:MI:SS'),'DD-MON-RR HH24:MI:SS') mkt_date,

       rdp.mkt_price,
       0,
       0,
       0,
       0,
       'Y',
       rdp.create_date,
       rdp.modify_date,


       -999,

       -999

  FROM rb_hour_price_history rdp
 WHERE rdp.history_date <= l_import_until_date    AND rdp.fp_code = fp;

   errors NUMBER;
   dml_errors EXCEPTION;
   PRAGMA exception_init(dml_errors, -24381);

BEGIN
   FOR c1rec IN c1 LOOP

      OPEN c2(c1rec.fp_code, c1rec.ppd_id, l_import_until_date);
      LOOP
         FETCH c2 bulk collect into
            src_history_date_array   ,
            src_ppdid_array          ,
            src_mkt_date_array       ,
            src_mkt_price_array      ,
            src_price_ajd1_array     ,
            src_price_ajd2_array     ,
            src_price_ajd3_array     ,
            src_price_ajd4_array     ,
            src_was_processed_array  ,
            src_create_date_array    ,
            src_modify_date_array    ,
            src_created_by_array     ,
            src_modified_by_array  LIMIT 700;

         BEGIN
            FORALL j IN 1 .. src_history_date_array.count SAVE
EXCEPTIONS
               INSERT INTO sys_price_archive (trading_day   ,
                                              ppd_id        ,
                                              interval      ,
                                              price         ,
                                              price_adj1    ,
                                              price_adj2    ,
                                              price_adj3    ,
                                              price_adj4    ,
                                              was_processed ,
                                              date_created  ,
                                              date_modified ,
                                              created_by    ,
                                              modified_by)
               VALUES (src_history_date_array(j)   ,
                       src_ppdid_array(j)          ,
                       src_mkt_date_array(j)       ,
                       src_mkt_price_array(j)      ,
                       src_price_ajd1_array(j)     ,
                       src_price_ajd2_array(j)     ,
                       src_price_ajd3_array(j)     ,
                       src_price_ajd4_array(j)     ,
                       src_was_processed_array(j)  ,
                       src_create_date_array(j)    ,
                       src_modify_date_array(j)    ,
                       src_created_by_array(j)     ,
                       src_modified_by_array(j));
         EXCEPTION
            WHEN dml_errors THEN
               NULL;
         END;
         EXIT WHEN C2%NOTFOUND;
      END LOOP;
      CLOSE C2;
      COMMIT;
      Autonomous_Insert (c1rec.fp_code); --This inserts into a temp

table so that i can see how many rows have been inserted.

   END LOOP;
END;
/ Received on Mon Mar 27 2006 - 12:04:30 CST