multitable insert vs. normal inserts in a loop

Giganews Newsgroups
Subject: multitable insert vs. normal inserts in a loop
Posted by:  Kaly (akalis…@gazeta.pl)
Date: 9 Dec 2004

i'd be grateful if someone could tell me what is faster:
1) 5 normal inserts to 5 different tables in a loop
2) 1 multitable insert in a loop

it has to look like:

1)
for v_qry in (select * from source_table) loop
  insert into table_1 (p1,p2,p3,...) values (v1,v2,v3,...);
  insert into table_2 (p1,p2,p3,...) values (v1,v2,v3,...);
  insert into table_3 (p1,p2,p3,...) values (v1,v2,v3,...);
  insert into table_4 (p1,p2,p3,...) values (v1,v2,v3,...);
  insert into table_5 (p1,p2,p3,...) values (v1,v2,v3,...);

  /* extra instructions */
end loop;

2)
for v_qry in (select * from source_table) loop
  insert all into table_1 (p1,p2,p3,...) values (v1,v2,v3,...)
              into table_2 (p1,p2,p3,...) values (v1,v2,v3,...)
              into table_3 (p1,p2,p3,...) values (v1,v2,v3,...)
              into table_4 (p1,p2,p3,...) values (v1,v2,v3,...)
              into table_5 (p1,p2,p3,...) values (v1,v2,v3,...)
          select * from source_table where id = v_qry.id;

    /* extra instructions */
end loop;

and the solution without the loop is impossible to use 'cause i have
some extra instructions in it

thanks for any suggestions
And.

Replies