Re: efficienci CURSOR+INSERT vs. INSERT INTO ... SELECT

"Francois Berger" <berger_at_fire.shacknet.nu> wrote in message news:Pine.LNX.4.44.0307011152300.11426-100000_at_fire.shacknet.nu...
> Hi all,
> I have a large amount of data issued from a select to insert into a table
> Currently, I am doing a loop with a cursor and an inser statement.
> Looks like:
> CURSOR my_cursor is ...;
> FOR my_record in my_cursor LOOP
> INSERT INTO ... VALUES (my_record.name, my_record.size, ...);
> END LOOP;
>
> I was wondering if something like INSERT INTO ... SELECT... was not more
> efficient:
> INSERT INTO... SELECT name, size FROM....;

If you do the insert once as opposed to n times it should always be faster, in addition generally (already parsed) SQL is faster than PL/SQL. This leads to the following (I think conclusive test

SQL> truncate table t1;

Table truncated.

SQL> desc t1;

 Name                                      Null?    Type
 ----------------------------------------- -------- ------------------------
----
 T_NAME                                    NOT NULL VARCHAR2(30)
 T_SIZE                                             NUMBER

SQL> set timing on
SQL> insert into t1 select object_name,object_id from all_objects;

39042 rows created.

Elapsed: 00:00:04.03
SQL> rollback;

Rollback complete.

Elapsed: 00:00:00.00
SQL> insert into t1 select object_name,object_id from all_objects;

39042 rows created.

Elapsed: 00:00:02.01
SQL> rollback;

Rollback complete.

Elapsed: 00:00:00.00
SQL> insert into t1 select object_name,object_id from all_objects;

39042 rows created.

Elapsed: 00:00:02.02
SQL> rollback;

Rollback complete.

Elapsed: 00:00:00.00
SQL> declare
  2 CURSOR my_cursor is select object_name,object_id from all_objects;   3 begin
  4 FOR my_record in my_cursor LOOP
  5 INSERT INTO t1 VALUES (my_record.object_name, my_record.object_id);   6 END LOOP;
  7 end;
  8 /

PL/SQL procedure successfully completed.

Elapsed: 00:00:10.02
SQL> rollback;

Rollback complete.

Elapsed: 00:00:00.09
SQL> declare
  2 CURSOR my_cursor is select object_name,object_id from all_objects;   3 begin
  4 FOR my_record in my_cursor LOOP
  5 INSERT INTO t1 VALUES (my_record.object_name, my_record.object_id);
  6 END LOOP;
  7 end;
  8 /
  9 .
SQL> declare
  2 CURSOR my_cursor is select object_name,object_id from all_objects;   3 begin
  4 FOR my_record in my_cursor LOOP
  5 INSERT INTO t1 VALUES (my_record.object_name, my_record.object_id);   6 END LOOP;
  7 end;
  8 /

PL/SQL procedure successfully completed.

Elapsed: 00:00:09.07
SQL> 2 secs beats 9 secs by a considerable margin.

-- 
Niall Litchfield
Oracle DBA
Audit Commission UK