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

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 rA.FIRST..rA.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.