Insert Menggunakan Bulk

Salah satu cara mempercepat proses insert adalah dengan metoda insert Bulk. Dapat dibuktikan dengan metoda konvensial. (Dapat lebih cepat 30 kali)

Demikian

SET SERVEROUTPUT ON
DECLARE
TYPE prod_tab IS TABLE OF products%ROWTYPE;
products_tab   prod_tab := prod_tab();
start_time  number;  end_time   number;
BEGIN
-- Populate a collection - 100000 rows
SELECT * BULK COLLECT INTO products_tab FROM products;

EXECUTE IMMEDIATE 'TRUNCATE TABLE products';
Start_time := DBMS_UTILITY.get_time;
FOR i in products_tab.first .. products_tab.last LOOP
 INSERT INTO products (product_id, product_name, effective_date)
   VALUES (products_tab(i).product_id, products_tab(i).product_name,
           products_tab(i).effective_date);
END LOOP;
end_time := DBMS_UTILITY.get_time;
DBMS_OUTPUT.PUT_LINE(‘Conventional Insert: ’||to_char(end_time-start_time));

EXECUTE IMMEDIATE 'TRUNCATE TABLE products';
Start_time := DBMS_UTILITY.get_time;
FORALL i in products_tab.first .. products_tab.last
 INSERT INTO products VALUES products_tab(i);
end_time := DBMS_UTILITY.get_time;
DBMS_OUTPUT.PUT_LINE(‘Bulk Insert: ’||to_char(end_time-start_time));
COMMIT;
END;