Optimizing PL/SQL Code - Oracle Database 11gR2 Performance Tuning - page 252

3. Populate the MY_CUSTOMERS table using an INSERT statement inside a FOR loop:

SET TIMING ON
BEGIN
FOR aRow IN (SELECT CUST_ID, CUST_FIRST_NAME FROM CUSTOMERS)
LOOP
INSERT INTO sh.MY_CUSTOMERS (CUST_ID, CUST_FIRST_NAME)
VALUES (aRow.CUST_ID, aRow.CUST_FIRST_NAME);
END LOOP;
END;
/
SET TIMING OFF

4. Truncate the MY_CUSTOMERS table to empty it:

TRUNCATE TABLE sh.MY_CUSTOMERS;

5. Create a custom datatype T_ID to store a table of numbers:

CREATE OR REPLACE TYPE sh.T_ID AS TABLE OF NUMBER;

6. Create a custom datatype T_NAME to store a table of varchars :

CREATE OR REPLACE TYPE sh.T_NAME AS TABLE OF VARCHAR2(20);

7. Populate the MY_CUSTOMERS table by retrieving the data from the CUSTOMERS

table with a BULK COLLECT statement and using a FORALL statement to insert
the records as a whole:
SET TIMING ON
DECLARE
TAB_ID T_ID;
TAB_NAME T_NAME;
BEGIN
SELECT CUST_ID, CUST_FIRST_NAME
BULK COLLECT INTO TAB_ID, TAB_NAME
FROM CUSTOMERS;
FORALL J IN TAB_ID.FIRST..TAB_ID.LAST
INSERT INTO sh.MY_CUSTOMERS (CUST_ID, CUST_FIRST_NAME)
VALUES (TAB_ID(J), TAB_NAME(J));
END;
/
SET TIMING OFF