Wednesday, July 8, 2015

Bulk vs Conventional data Loading in Oracle for Large tables


DECLARE
TYPE t_bas_user_log IS TABLE OF BAS_USER_LOG%ROWTYPE;
myuser_tab   t_bas_user_log := t_bas_user_log();
start_time  number;
end_time   number;
BEGIN
-- Populate a collection - 100000 rows
SELECT * BULK COLLECT INTO myuser_tab FROM base_user_log1;
EXECUTE IMMEDIATE 'TRUNCATE TABLE myuser_log';
Start_time := DBMS_UTILITY.get_time;
FOR i in myuser_tab.first .. myuser_tab.last LOOP
 INSERT INTO myuser_log --(product_id, product_name, effective_date)
 VALUES  (myuser_tab(i).LOG_SERIAL_NUM      ,
myuser_tab(i).LOG_USER_ID        ,
  myuser_tab(i).LOG_ACTION_TYPE     ,
  myuser_tab(i).LOG_DATE           ,
  myuser_tab(i).TABLE_NAME         ,
  myuser_tab(i).FIELD_NAME_STRING  ,
  myuser_tab(i).OLD_VALUE_STRING   ,
  myuser_tab(i).NEW_VALUE_STRING   ,
  myuser_tab(i).CMP_NUM             ,
  myuser_tab(i).TRANS_SERIAL        ,
  myuser_tab(i).ACCOUNT_YEAR  )    ;
END LOOP;
end_time := DBMS_UTILITY.get_time;
DBMS_OUTPUT.PUT_LINE('Conventional Insert: '||to_char(end_time-start_time));
EXECUTE IMMEDIATE 'TRUNCATE TABLE myuser_log';
Start_time := DBMS_UTILITY.get_time;
FORALL i in myuser_tab.first .. myuser_tab.last
 INSERT INTO myuser_log VALUES myuser_tab(i);
end_time := DBMS_UTILITY.get_time;
DBMS_OUTPUT.PUT_LINE('Bulk Insert: '||to_char(end_time-start_time));
    COMMIT;
END;

Image below shows that there is a huge difference between Bulk and conventional data loading.



No comments:

Post a Comment