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