Link to home
Start Free TrialLog in
Avatar of dba_shashi
dba_shashiFlag for United States of America

asked on

Taking too much time Inserting bulk load in oracle.

Hi Experts
Please have a look at below script and help me load data faster. I have over 600 Million Records to be inserted
.
Many Thanks

CREATE OR REPLACE PROCEDURE GSDWRPTUSR.proc_ods_data_load (
   rec_ret_code   OUT   NUMBER,
   xreturn_msg    OUT   VARCHAR2
)
AS
   TYPE rec_acct IS RECORD (
      unique_acct_cd   gsdwrptusr.stag_tab_ods_dly.unique_acct_cd%TYPE,
      pos_date         gsdwrptusr.stag_tab_ods_dly.pos_date%TYPE
   );

   TYPE rec_accttype IS TABLE OF rec_acct;

   c_rec_accttype   rec_accttype;

--CURSOR FOR GETTING THE RECORDS FOR ACCT_HIST
   CURSOR accthistload
   IS
      SELECT DISTINCT unique_acct_cd, MIN (pos_date)
                 FROM stag_tab_ods_dly
             GROUP BY unique_acct_cd;

   TYPE rec_issu IS RECORD (
      unique_sec_cd        gsdwrptusr.stag_tab_ods_dly.unique_sec_cd%TYPE,
      reg_cusip            gsdwrptusr.stag_tab_ods_dly.reg_cusip%TYPE,
      ticker               gsdwrptusr.stag_tab_ods_dly.ticker%TYPE,
      sec_issue_nm         gsdwrptusr.stag_tab_ods_dly.sec_issue_nm%TYPE,
      gic_sector           gsdwrptusr.stag_tab_ods_dly.gic_sector%TYPE,
      gic_industry         gsdwrptusr.stag_tab_ods_dly.gic_industry%TYPE,
      sec_type_cd          gsdwrptusr.stag_tab_ods_dly.sec_type_cd%TYPE,
      mat_date             gsdwrptusr.stag_tab_ods_dly.mat_date%TYPE,
      curr_cd              gsdwrptusr.stag_tab_ods_dly.curr_cd%TYPE,
      price_multiplier     gsdwrptusr.stag_tab_ods_dly.price_multiplier%TYPE,
      country_cd           gsdwrptusr.stag_tab_ods_dly.country_cd%TYPE,
      coupon_rate          gsdwrptusr.stag_tab_ods_dly.coupon_rate%TYPE,
      shares_outstanding   gsdwrptusr.stag_tab_ods_dly.shares_outstanding%TYPE,
      inv_type_cd          gsdwrptusr.stag_tab_ods_dly.inv_type_cd%TYPE,
      pos_date             gsdwrptusr.stag_tab_ods_dly.pos_date%TYPE
   );

   TYPE rec_issutype IS TABLE OF rec_issu;

   c_rec_issutype   rec_issutype;

--CURSOR FOR GETTING THE RECORDS FOR ISSU_HIST
   CURSOR issuhistload
   IS
      SELECT DISTINCT unique_sec_cd, reg_cusip, ticker, sec_issue_nm,
                      gic_sector, gic_industry, sec_type_cd, mat_date,
                      curr_cd, price_multiplier, country_cd, coupon_rate,
                      shares_outstanding, inv_type_cd, MIN (pos_date)
                                                                     pos_date
                 FROM gsdwrptusr.stag_tab_ods_dly
             GROUP BY unique_sec_cd,
                      reg_cusip,
                      ticker,
                      sec_issue_nm,
                      gic_sector,
                      gic_industry,
                      sec_type_cd,
                      mat_date,
                      curr_cd,
                      price_multiplier,
                      country_cd,
                      coupon_rate,
                      shares_outstanding,
                      inv_type_cd
             ORDER BY pos_date;

   --CURSOR FOR GETTING THE RECORDS FOR POSN_HIST
   TYPE rec_posn IS RECORD (
      sec_sok           gsdwrptusr.issu_hist.sec_sok%TYPE,
      acct_sok          gsdwrptusr.acct_hist.acct_sok%TYPE,
      pos_date          gsdwrptusr.stag_tab_ods_dly.pos_date%TYPE,
      prc_base          gsdwrptusr.stag_tab_ods_dly.prc_base%TYPE,
      prc_local         gsdwrptusr.stag_tab_ods_dly.prc_local%TYPE,
      base_cost         gsdwrptusr.stag_tab_ods_dly.base_cost%TYPE,
      local_cost        gsdwrptusr.stag_tab_ods_dly.local_cost%TYPE,
      share_par_value   gsdwrptusr.stag_tab_ods_dly.share_par_value%TYPE,
      spot_rate         gsdwrptusr.stag_tab_ods_dly.spot_rate%TYPE,
      sec_issue_nm      gsdwrptusr.stag_tab_ods_dly.sec_issue_nm%TYPE
   );

   TYPE rec_posntype IS TABLE OF rec_posn;

   c_rec_posntype   rec_posntype;

   CURSOR posnhistload
   IS
      SELECT   (SELECT sec_sok
                  FROM issu_hist
                 WHERE sec_id = c.unique_sec_cd AND ROWNUM = 1),
               (SELECT acct_sok
                  FROM acct_hist
                 WHERE acct_id = c.unique_acct_cd AND ROWNUM = 1), c.pos_date,
               c.prc_base, c.prc_local, c.base_cost, c.local_cost,
               c.share_par_value, c.spot_rate, c.sec_issue_nm
          FROM stag_tab_ods_dly c
      ORDER BY c.pos_date;

   exit_on_error    EXCEPTION;
BEGIN
   EXECUTE IMMEDIATE 'ALTER SESSION ENABLE PARALLEL DML';

   DECLARE
      exit_on_error   EXCEPTION;
   BEGIN
      rec_ret_code := 0;

      --RAISE EXCEPTION IN CASE OF FAILURE
      IF rec_ret_code != 0
      THEN
         RAISE exit_on_error;
      END IF;

      OPEN accthistload;

      LOOP
         FETCH accthistload
         BULK COLLECT INTO c_rec_accttype LIMIT 100000;

         DBMS_OUTPUT.put_line (   'Iteration count for acct_hist -'
                               || TO_CHAR (c_rec_accttype.COUNT)
                              );
         FORALL i IN 1 .. c_rec_accttype.COUNT
            INSERT      /*+ parallel (6,1) */INTO acct_hist
                        (acct_id, acct_sok,
                         data_src_id,
                         version_start_tmsmp,
                         version_end_tmsmp,
                         crte_tstp,
                         crte_pgm
                        )
                 VALUES (c_rec_accttype (i).unique_acct_cd, acct_seq.NEXTVAL,
                         'O',
                         TO_TIMESTAMP (c_rec_accttype (i).pos_date,
                                       'DD-MON-RR HH.MI.SS.FF AM'
                                      ),
                         TO_TIMESTAMP ('30-SEP-11 12.00.00.000000000 AM',
                                       'DD-MON-RR HH.MI.SS.FF AM'
                                      ),
                         TO_TIMESTAMP (SYSDATE, 'DD-MON-RR HH.MI.SS.FF AM'),
                         'BATCH'
                        );
         COMMIT;
         EXIT WHEN accthistload%NOTFOUND;
      END LOOP;

      --RAISE EXCEPTION IN CASE OF FAILURE
      IF rec_ret_code != 0
      THEN
         RAISE exit_on_error;
      END IF;

      OPEN issuhistload;

      LOOP
         FETCH issuhistload
         BULK COLLECT INTO c_rec_issutype LIMIT 100000;

         DBMS_OUTPUT.put_line (   'Iteration count for issu_hist -'
                               || TO_CHAR (c_rec_issutype.COUNT)
                              );
         FORALL i IN 1 .. c_rec_issutype.COUNT
            INSERT      /*+ parallel (6,1) */INTO gsdwrptusr.issu_hist hist
                        (hist.sec_sok, hist.sec_id,
                         hist.data_src_id, hist.version_start_tmsmp,
                         hist.version_end_tmsmp,
                         hist.crte_tstp, hist.crte_pgm, hist.cusip_id,
                         hist.ticker_id,
                         hist.pref_iss_nme,
                         hist.gics_sector_cde,
                         hist.gics_indus_cde,
                         hist.iss_typ,
                         hist.mat_exp_tmsmp,
                         hist.denom_curr_cde,
                         hist.prc_mltplr_crte,
                         hist.issuance_cntry_nme,
                         hist.cpn_crte,
                         hist.shr_outst_cqty,
                         hist.inv_type_cde
                        )
                 VALUES (issu_seq.NEXTVAL, c_rec_issutype (i).unique_sec_cd,
                         'O', c_rec_issutype (i).pos_date,
                         TO_TIMESTAMP ('30-SEP-11 12.00.00',
                                       'DD-MON-RR HH.MI.SS'
                                      ),
                         SYSDATE, 'BATCH', c_rec_issutype (i).reg_cusip,
                         c_rec_issutype (i).ticker,
                         c_rec_issutype (i).sec_issue_nm,
                         c_rec_issutype (i).gic_sector,
                         c_rec_issutype (i).gic_industry,
                         c_rec_issutype (i).sec_type_cd,
                         c_rec_issutype (i).mat_date,
                         c_rec_issutype (i).curr_cd,
                         c_rec_issutype (i).price_multiplier,
                         c_rec_issutype (i).country_cd,
                         c_rec_issutype (i).coupon_rate,
                         c_rec_issutype (i).shares_outstanding,
                         c_rec_issutype (i).inv_type_cd
                        );
         COMMIT;
         EXIT WHEN issuhistload%NOTFOUND;
      END LOOP;

      CLOSE issuhistload;

      INSERT      /*+ parallel (6,1) */INTO issu_temp_tab
                  (sec_id, sec_sok, version_start_tmsmp, version_end_tmsmpn)
         SELECT DISTINCT b.sec_id, b.sec_sok, b.version_start_tmsmp,
                         (a.version_start_tmsmp - 1) version_end_tmsmp_new
                    FROM gsdwrptusr.issuview a, gsdwrptusr.issuview b
                   WHERE a.sec_id = b.sec_id
                     AND b.ranking = a.ranking + 1
                     AND a.ranking > 0
                ORDER BY version_start_tmsmp DESC;

      --INSERTING VALUES IN TO POSN_HIST TABLE
      OPEN posnhistload;

      LOOP
         FETCH posnhistload
         BULK COLLECT INTO c_rec_posntype LIMIT 100000;

         DBMS_OUTPUT.put_line (   'Iteration count for posn_hist -'
                               || TO_CHAR (c_rec_posntype.COUNT)
                              );
         FORALL i IN 1 .. c_rec_posntype.COUNT
            INSERT INTO gsdwrptusr.posn_hist
                        (pos_sok, acct_sok,
                         sec_sok, data_src_id,
                         crte_tstp,
                         crte_pgm,
                         holding_dte,
                         bkpg_curr_book_cprc,
                         local_curr_book_cprc,
                         bkpg_unit_cost_camt,
                         local_unit_cost_camt,
                         qty_cqty,
                         spot_rate
                        )
                 VALUES (posn_seq.NEXTVAL, c_rec_posntype (i).acct_sok,
                         c_rec_posntype (i).sec_sok, 'O',
                         TO_TIMESTAMP (SYSDATE, 'DD-MON-RR HH.MI.SS.FF AM'),
                         'BATCH',
                         TO_TIMESTAMP (c_rec_posntype (i).pos_date,
                                       'DD-MON-RR HH.MI.SS.FF AM'
                                      ),
                         c_rec_posntype (i).prc_base,
                         c_rec_posntype (i).prc_local,
                         c_rec_posntype (i).base_cost,
                         c_rec_posntype (i).local_cost,
                         c_rec_posntype (i).share_par_value,
                         c_rec_posntype (i).spot_rate
                        );
         COMMIT;
         EXIT WHEN posnhistload%NOTFOUND;
      END LOOP;

      CLOSE posnhistload;

      proc_issu_update ();
--THIS HAS TO BE UN-COMMENTED POST TESTING THE DATA

   --DELETE FROM ISSU_TEMP_TAB
--DELETE FROM STG_ACCT;

   --CATCH THE RAISED EXCEPTION AND ROLLBACK THE TRANSACTION
--ALSO SHOW THE EXCEPTION MESSAGE.
   EXCEPTION
      WHEN exit_on_error OR NO_DATA_FOUND
      THEN
         BEGIN
            ROLLBACK;
            rec_ret_code := 1;
            xreturn_msg := SUBSTR (SQLERRM, 1, 256);
         END;
      WHEN OTHERS
      THEN
         BEGIN
            ROLLBACK;
            rec_ret_code := 1;
            xreturn_msg := SUBSTR (SQLERRM, 1, 256);
         END;
   END;
END;
/
ASKER CERTIFIED SOLUTION
Avatar of oleggold
oleggold
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Avatar of schwertner
schwertner
Flag of Antarctica image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of dba_shashi

ASKER

Hi
Thanks for these valuable suggestion.
But all the requirement above is necessity here and nothing should be changed. Please guide me if any modification can be done further?