Home » SQL & PL/SQL » SQL & PL/SQL » differnece b/w bulk collect/forall and bulk collect/for (oracle 10.2)
differnece b/w bulk collect/forall and bulk collect/for [message #345261] Tue, 02 September 2008 14:31 Go to next message
pdhayan@qwest.com
Messages: 6
Registered: August 2008
Junior Member
Hi,
What is the difference between bulk collect/forall & bulk collect/for?
bulk collect/forall runs much faster than bulk collect/for, but I need to know the difference b/w them in terms of resource consumed.

I greatly appreciate your help in making me understand the difference b/w the two methods.

Thanks
Channa
Re: differnece b/w bulk collect/forall and bulk collect/for [message #345266 is a reply to message #345261] Tue, 02 September 2008 14:36 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
FOR is a loop.
FORALL is a single statement.

Details in
PL/SQL User's Guide and Reference
Application Developer's Guide - Fundamentals

Regards
Michel
Re: differnece b/w bulk collect/forall and bulk collect/for [message #345268 is a reply to message #345261] Tue, 02 September 2008 14:47 Go to previous messageGo to next message
pdhayan@qwest.com
Messages: 6
Registered: August 2008
Junior Member
Thanks Michel.

Is there any difference in the way memory being used by forall & for?

Lets say the in bulk collect I fetch 10000 rows & forall commits after loading the 10000 & for loop also commits after loading 10000 rows.

Re: differnece b/w bulk collect/forall and bulk collect/for [message #345270 is a reply to message #345268] Tue, 02 September 2008 14:55 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Memory is used by bulk collect to load the rows, no relation with forall or for.

Regards
Michel
Re: differnece b/w bulk collect/forall and bulk collect/for [message #345323 is a reply to message #345270] Wed, 03 September 2008 00:26 Go to previous message
harrysmall3
Messages: 109
Registered: April 2008
Location: Massachusetts
Senior Member
I would be interested to see the INSERT or UPDATE speed difference between the FORALL and a loop against the result set with one per record.

I'd bulk collect in both instances, to have the results in an
identical array to start vs single fetching from a cursor, as a control.

From recent experience, the overhead of session memory in the bulk fetch can cause a CPU spike but I'm sure many factor's contribute - I just couldnt find one when the DBA's knocked on my door after an SQL performance enhancement I made in these
regards. (this was version 9.2.0.3)

I had to scale back the collect size using the LIMIT clause.
Nonetheless, on the DB side the performance gain was a success!

Regards,
Harry

Previous Topic: How to interesting select *
Next Topic: Functions
Goto Forum:
  


Current Time: Sat Dec 10 01:02:43 CST 2016

Total time taken to generate the page: 0.10364 seconds