Home » SQL & PL/SQL » SQL & PL/SQL » Usage of Bulk bind in Select SQL
Usage of Bulk bind in Select SQL [message #282620] Thu, 22 November 2007 10:46 Go to next message
bhuvan_oracle
Messages: 6
Registered: November 2007
Location: Chennai
Junior Member

Hi

I need the practical usage of BULK Bind, is there any more than specific records to fetch means we need to use BULK Bind?
otherwise we can use normal Looping Fetching Conditions
Please let me know in which scenerio we need to use BULK BIND

Thank you
Bhuvan

Re: Usage of Bulk bind in Select SQL [message #282644 is a reply to message #282620] Thu, 22 November 2007 20:41 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Bulk bind is faster than looping, so you use when you are processing large quantities of data.

Ross Leishman
Re: Usage of Bulk bind in Select SQL [message #282662 is a reply to message #282620] Thu, 22 November 2007 22:45 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

And is basically used in PL/SQL Construct Not in SQL directly

Thumbs Up
Rajuivan
Re: Usage of Bulk bind in Select SQL [message #282686 is a reply to message #282620] Fri, 23 November 2007 00:36 Go to previous messageGo to next message
Michel Cadot
Messages: 64122
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
First, only think PL/SQL if you can't do it in SQL.
Then, each time you think loop think bulk select.
There is no use of old loop fashion.

Regards
Michel


Re: Usage of Bulk bind in Select SQL [message #282803 is a reply to message #282620] Fri, 23 November 2007 10:19 Go to previous messageGo to next message
bhuvan_oracle
Messages: 6
Registered: November 2007
Location: Chennai
Junior Member

I Know that the Bulk collect is useful in PL/SQL and also faster in
Looping. But my question is normally i have seen in the code in plsql normal way of Cursor fetching only used(it is not used with bulk collect at a time). why is it so? whether do we need change to bulk collect? or only for large amount of records only we need to use bulk collect?. Please note that the normal fetching used to get 500 to 1000 records.
Please guide me.

Thank you
Bhuvan

Re: Usage of Bulk bind in Select SQL [message #282903 is a reply to message #282686] Sat, 24 November 2007 23:02 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Michel Cadot wrote on Fri, 23 November 2007 17:36


There is no use of old loop fashion.

Michel and I may have to agree to disagree here.

Implicit cursor loops are a very intuitive construct and involve much less code than the equivalent bulk-collect syntax, which because of its complexity, invites more programming errors.

In an OLTP environment where you tend to loop over only a few rows, the performance cost of implicit cursor loops may be minimal or possibly even undetectable.

If you are looping over 1000 rows, you need to ask a few questions:
- How often is it run?
- Is it likely to grow?
- How important is performance?

If it is run more than once per hour, or if you cannot control future growth, or if you are desperate to squeeze that last nanosecond of performance out of it, then you should consider bulk collect/bind.

The reason you don't see bulk collect bind that much is because it was not present in earlier versions of PL/SQL, and also because it is harder and longer to code.

Ross Leishman

[Updated on: Sat, 24 November 2007 23:03]

Report message to a moderator

Re: Usage of Bulk bind in Select SQL [message #282931 is a reply to message #282903] Sun, 25 November 2007 03:27 Go to previous message
Michel Cadot
Messages: 64122
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:

In an OLTP environment where you tend to loop over only a few rows, the performance cost of implicit cursor loops may be minimal or possibly even undetectable.

In OLTP these loops are likely to be executed many and many times. Many of small stones make montains.
Context switches are evil. They slow down not only you but also others.

Regards
Michel
Previous Topic: validation in sql loader
Next Topic: update query performance
Goto Forum:
  


Current Time: Wed Dec 07 04:56:04 CST 2016

Total time taken to generate the page: 0.05639 seconds