How to use BULK_COLLECT instead of insert into select

Question

I have been searching the web, I'm surprised to see same example everywhere and is always all_objects. Not being good in advanced SQL, I don't understand how do I convert this statement to use BULK_COLLECT.

INSERT INTO bpm_staging ( 
    id, pay_obj, merchant, address,
    city, country,debit_num, first_name,
    state, zip, id_type,bank_no)
  SELECT 
    p.account_obj, p.id, p.ach, pc.address,
    pc.city, pc.country, pc.debit_num, pc.name,
    pc.state, pc.zip, p.id_type,pc.bank_no
  FROM au_pay_t p, au_pay_dd_t pc 
  WHERE p.id= pc.obj;

I used the above statement because , using cursors take longer. Also using the Select is much easier to understand for anyone who has to modify your query.

However, my DBA rejected to run on Production because this will take long time to insert row-by-row 540K records.

When I search through the web , I found BULK_COLLECT /FORALL is better option as far performance is concerned.

Please suggest a better workaround. If BULK_COLLECT is a good option then please suggest a good example of this kind.

Answer 1

1. Define a appropriate PL/SQL types (both a record type and a nested table type) to collect the results:

   TYPE my_type IS RECORD (
       account_obj au_pay_t.account_obj%TYPE,
       id          au_pay_t.id%TYPE,
       /* ... */
       bank_no     au_pay_dd_t.bank_no%TYPE
   );
   
   TYPE my_tab_type IS TABLE OF my_type;
   

2. Declare a PL/SQL variable with the above defined nested table type:

   DECLARE
     my_tab my_tab_type;
   

3. Use your variable to store the query results:

   SELECT
     p.account_obj, p.id, p.ach, pc.address,
     pc.city, pc.country, pc.debit_num, pc.name,
     pc.state, pc.zip, p.id_type,pc.bank_no
   BULK COLLECT INTO my_tab
   FROM au_pay_t p, au_pay_dd_t pc
   WHERE p.id = pc.obj;
   

   • Be aware that the result set may be large; do not select more than, say a thousand records at a time. See Doing SQL from PL/SQL: Best and Worst Practices for more advanced techniques.

4. Finally, loop on the results to insert them into your table

   FORALL i IN 1..my_tab.COUNT
     INSERT INTO bpm_staging ( 
         id, pay_obj, merchant, address,
         city, country,debit_num, first_name,
         state, zip, id_type,bank_no)
     VALUES (
         my_tab(i).account_obj, my_tab(i).id,
         ..., my_tab(i).bank_no);
   

   • Warning: the above construct will only work on Oracle 11g or above

Answer 2

insert /*+ append */ into

might also speed it up.