0

For performance reasons, I want to rewrite the following to use BULK COLLECT and FORALL, rather than doing the inserts in a loop. The problem I'm running in to, is that empID must be generated on each iteration of the loop, or I need to do something similar with BULK COLLECT to create a collection to use FORALL on.

...

FOR i in 1 .. numberOfEmployeesToAdd
LOOP

    BEGIN

    empID := EMPLOYEE_SEQ.NEXTVAL;

    INSERT INTO EMPLOYEE (ID,FIRST_NAME,LAST_NAME) 
        VALUES (empID, 'firstNameTest', 'lastNameTest');

    INSERT INTO EMPLOYEE_DEPT_ASSOC (ID, DEPT_ID, EMP_ID)
        VALUES (EMPLOYEE_DEPT_ASSOC_SEQ.NEXTVAL, '247', empID);

    INSERT INTO SKILLSET (ID, EMP_ID, SKILL_ID)
        VALUES (SKILLSET_ASSOC.NEXTVAL, empID, '702');

    END;

END LOOP;

The examples of BULK COLLECT and FORALL seem to mostly consist of creating a cursor where you do select * from [some table] and then fetch that cursor and do a BULK COLLECT. But, I need to somehow dynamically assign values within a cursor using the next contiguous 'numberOfEmployeesToAdd' number of IDs and then do a FORALL to do the inserts.

Maheswaran Ravisankar
  • 17,652
  • 6
  • 47
  • 69
Plant More Trees
  • 65
  • 1
  • 1
  • 9

1 Answers1

0

Wont this help you? If you have a nested table with your set of data, you can join it to the SELECT

INSERT INTO EMPLOYEE (ID,FIRST_NAME,LAST_NAME) 
   SELECT EMPLOYEE_SEQ.NEXTVAL, 'firstNameTest', 'lastNameTest'
     FROM DUAL
   CONNECT BY LEVEL <= numberOfEmployeesToAdd;


INSERT INTO EMPLOYEE_DEPT_ASSOC (ID, DEPT_ID, EMP_ID)
    SELECT EMPLOYEE_DEPT_ASSOC_SEQ.NEXTVAL, '247', ID
    FROM EMPLOYEE;

INSERT INTO SKILLSET (ID, EMP_ID, SKILL_ID)
    SELECT SKILLSET_ASSOC.NEXTVAL, ID, '702'
    FROM EMPLOYEE;
Maheswaran Ravisankar
  • 17,652
  • 6
  • 47
  • 69
  • Does this give me a performance improvement over how I'm doing it currently? The reason I need to improve the performance is because I'll be inserting around 2 million employees to use for performance testing. Right now, the way I've written it, it takes about 8 minutes for 100k, and it actually runs out of memory when I try to do around 500k. So, I need to find a way to improve the performance. – Plant More Trees Jun 19 '14 at 19:58
  • insert select is more performant than pl sql bulk insert.... And forall (bulk) insert is better and insert in a normal for loop.. Insert select is db friendly. – Maheswaran Ravisankar Jun 19 '14 at 23:38
  • It runs out of memory because.. All the data in collection uses up the sga stotage only. And if you are concerned about performance with INSerts, you can explore sql loader.. It is an utility from oracle, optimised for inserting into tables.. You might need to generate files out of the select queries and feed that to the sql loader utility to load the table then.. It is far far bette than pl sql.. Pl sql is not an good idea for inserting into table when the operation is huge – Maheswaran Ravisankar Jun 20 '14 at 00:01