| |
PL/SQL forall operator speeds 30x faster for table
inserts
Loading an Oracle table from a PL/SQL array involves expensive
context switches, and the PL/SQL FORALL operator speed is amazing.
The best overall reference of hypercharging PL/SQL table insert
performance with forall array collections is Dr. Tim Hall (Oracle ACE of
the year 2006) and his landmark book "Oracle
PL/SQL Tuning: Expert Secrets for High Performance Programming".
Kent Crotty, author of
Easy
Oracle Application Express (HTML-DB) conducted a study to prove
the speed of the PL/SQL forall over vanilla SQL inserts, and found
that FORALL was 30x faster in his small test:
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;
Crotty notes a great speed improvement with very few code
changes, from 622 seconds to only 22 seconds:
SQL> /
Conventional Insert: 686
Bulk Insert: 22
Here are my related notes on the PL/SQL FORALL operator speed:
|
|
