Bulk Insert into Oracle database: Which is better: FOR Cursor loop or a simple Select ?

Question

Which would be a better option for bulk insert into an Oracle database ? A FOR Cursor loop like

DECLARE
   CURSOR C1 IS SELECT * FROM FOO;
BEGIN
   FOR C1_REC IN C1 LOOP
   INSERT INTO BAR(A,
                B,
                C)
          VALUES(C1.A,
                 C1.B,
                 C1.C);
   END LOOP;
END

or a simple select, like:

INSERT INTO BAR(A,
                B,
                C)
        (SELECT A,
                B,
                C
        FROM FOO);

Any specific reason either one would be better ?

Answer 1

I would recommend the Select option because cursors take longer.
Also using the Select is much easier to understand for anyone who has to modify your query

Answer 2

If your rollback segment/undo segment can accomodate the size of the transaction then option 2 is better. Option 1 is useful if you do not have the rollback capacity needed and have to break the large insert into smaller commits so you don't get rollback/undo segment too small errors.

Answer 3

A simple insert/select like your 2nd option is far preferable. For each insert in the 1st option you require a context switch from pl/sql to sql. Run each with trace/tkprof and examine the results.

If, as Michael mentions, your rollback cannot handle the statement then have your dba give you more. Disk is cheap, while partial results that come from inserting your data in multiple passes is potentially quite expensive. (There is almost no undo associated with an insert.)

Answer 4

The general rule-of-thumb is, if you can do it using a single SQL statement instead of using PL/SQL, you should. It will usually be more efficient.

However, if you need to add more procedural logic (for some reason), you might need to use PL/SQL, but you should use bulk operations instead of row-by-row processing. (Note: in Oracle 10g and later, your FOR loop will automatically use BULK COLLECT to fetch 100 rows at a time; however your insert statement will still be done row-by-row).

e.g.

DECLARE
   TYPE tA IS TABLE OF FOO.A%TYPE INDEX BY PLS_INTEGER;
   TYPE tB IS TABLE OF FOO.B%TYPE INDEX BY PLS_INTEGER;
   TYPE tC IS TABLE OF FOO.C%TYPE INDEX BY PLS_INTEGER;
   rA tA;
   rB tB;
   rC tC;
BEGIN
   SELECT * BULK COLLECT INTO rA, rB, rC FROM FOO;
   -- (do some procedural logic on the data?)
   FORALL i IN r.FIRST..r.LAST
      INSERT INTO BAR(A,
                      B,
                      C)
      VALUES(rA(i),
             rB(i),
             rC(i));
END;

The above has the benefit of minimising context switches between SQL and PL/SQL. Oracle 11g also has better support for tables of records so that you don't have to have a separate PL/SQL table for each column.

Also, if the volume of data is very great, it is possible to change the code to process the data in batches.