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,