Optimizing SQL Code - Oracle Database 11gR2 Performance Tuning - page 176

3. Enable timing:

SET TIMING ON

4. Create a PL/SQL block to insert the sales of the second half of year 2001 from the

SALES table to the new table using a cursor to scroll the SALES table:
DECLARE
CURSOR curs_c1 IS
SELECT cust_id, prod_id FROM sh.sales
WHERE time_id between TO_DATE('20010701', 'YYYYMMDD')
AND TO_DATE('20011231', 'YYYYMMDD');
BEGIN
FOR x IN curs_c1
LOOP
INSERT INTO sh.MY_SALES (cust_id, prod_id)
VALUES (x.cust_id, x.prod_id);
END LOOP;
END;

5. Create a PL/SQL block to insert the sales of the second half of 2001 from the

SALES table to the new table using ARRAYS to collect and insert data:
DECLARE
TYPE t_products_list IS TABLE OF
sh.sales.prod_id%TYPE INDEX BY BINARY_INTEGER;
TYPE t_customers_list IS TABLE OF
sh.customers.cust_id%TYPE INDEX BY BINARY_INTEGER;
products_list t_products_list;
customers_list t_customers_list;
BEGIN
SELECT cust_id, prod_id
BULK COLLECT INTO customers_list, products_list
FROM sh.sales
WHERE time_id between TO_DATE('20010701', 'YYYYMMDD')
AND TO_DATE('20011231', 'YYYYMMDD');
FORALL j IN 1 .. customers_list.COUNT
INSERT INTO sh.MY_SALES (cust_id, prod_id)
VALUES (customers_list(j), products_list(j));
END;