Home » RDBMS Server » Performance Tuning » Insert all vs Forall
Insert all vs Forall [message #213691] Thu, 11 January 2007 22:29 Go to next message
a_developer
Messages: 194
Registered: January 2006
Senior Member
Which one is better and why??

Insert all
     Into table2 (col1, col2)
     Into table3 (col3)
     Into table4 (col4)
     Into table5(col5)
Select col1, col2, col3, col4, col5 from table1;

versus
Forall I in (select col1,col2 from table1)
    Insert into table2 values(i);
Forall I in (select col3 from table1)
    Insert into table3 values(i);
Forall I in (select col4 from table1)
    Insert into table4 values(i);
Forall I in (select col5 from table1)
    Insert into table5 values(i);


Thanks in advance..
Re: Insert all vs Forall [message #213700 is a reply to message #213691] Thu, 11 January 2007 23:33 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>Which one is better and why??
Better based upon which metric?
CPU time, IOs, elasped timed,etc.
Benchmark all scenarios & decide for yourself.
Re: Insert all vs Forall [message #213718 is a reply to message #213691] Fri, 12 January 2007 01:00 Go to previous messageGo to next message
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
Senior Member
The second one will be faster, because it is syntactically incorrect - it will fail almost immediately. Very Happy

However, if you were to declare the SELECT as a CURSOR and BULK COLLECT it into a collection, you'd have something worth comparing.

Usually the 1st one will be better, because it accesses the source table only once. Especially if you add the /*+ APPEND */ hint. The INSERTS can also be parallellised

However, if you have separate indexes on each of the sets of columns you are inserting, and there are a lot of other columns on the table, then you might find the second one faster - maybe. It would be able to perform separate fast-full-scans of the indexes it needs, whereas the first one would perform a big full table scan.

A third option would be to
Insert /*+APPEND PARALLEL*/ into table2
select col1,col2 from table1;

Insert /*+APPEND PARALLEL*/ into table3
select col3 from table1;

Insert /*+APPEND PARALLEL*/ into table4
select col4 from table1;

Insert /*+APPEND PARALLEL*/ into table5
select col5 from table1;

Without the hints, this would be equivalent to the FORALL method. It might be comparable to the INSERT ALL if it could run off indexes though.

Ross Leishman
Re: Insert all vs Forall [message #213805 is a reply to message #213718] Fri, 12 January 2007 06:29 Go to previous message
a_developer
Messages: 194
Registered: January 2006
Senior Member
anacedent, I mean 'better' in terms of elapsed time.
Ross, thanks for the info and for finding the syntax error, but you're right - what I meant was the selected rows were actually bulk collected to a collection.
Previous Topic: Query performing good in 9i but very very slow in 10g
Next Topic: Different OPtimizer behaviour on Ora9iR2 ( patch 7 )-Shared server
Goto Forum:
  


Current Time: Wed Dec 11 22:42:48 CST 2024