Alex Rivera | Logout

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

Asked 2009-06-12T14:44:58.687
27

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 ?

sql oracle-database plsql
Edit
Report

2 Answers

5

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.)

answered 2009-06-12T18:31:35.540
0

You can use:

Bulk collect along with FOR ALL that is called Bulk binding.

Because PL/SQL forall operator speeds 30x faster for simple table inserts.

BULK_COLLECT and Oracle FORALL together these two features are known as Bulk Binding. Bulk Binds are a PL/SQL technique where, instead of multiple individual SELECT, INSERT, UPDATE or DELETE statements are executed to retrieve from, or store data in, at table, all of the operations are carried out at once, in bulk. This avoids the context-switching you get when the PL/SQL engine has to pass over to the SQL engine, then back to the PL/SQL engine, and so on, when you individually access rows one at a time. To do bulk binds with INSERT, UPDATE, and DELETE statements, you enclose the SQL statement within a PL/SQL FORALL statement. To do bulk binds with SELECT statements, you include the BULK COLLECT clause in the SELECT statement instead of using INTO.

It improves performance.

answered 2013-02-03T14:57:37.860

Your Answer