| Insert all vs Forall [message #213691] |
Thu, 11 January 2007 22:29  |
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 #213718 is a reply to message #213691] |
Fri, 12 January 2007 01:00  |
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. 
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 |
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.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
