|
|
|
|
|
|
| Re: difference in a bind variable and a PL/SQL variable [message #7309 is a reply to message #7308] |
Tue, 03 June 2003 20:12  |
Hasmukh Daji
Messages: 3
Registered: May 2003
|
Junior Member |
|
|
Anna,
I am no ORACLE geek. I had similar question in my mind couple of days ago. Here is a complete paragraph from O'Reilly's "Mastering Oracle SQL" book on Page 303; which in fact I referred to only this morning.
Using Bind Variables:
---------------------
When multiple users use an application, they actually execute the same set of SQL statements over and over, but with different data values. e.g. One customert service representative may be executing the following statements:
SELECT * FROM CUSTOMER WHERE CUST_NBR = 121;
while another customer service representative will be executing:
SELECT * FROM CUSTOMER WHERE CUST_NBR = 328;
These two statements are similar, but not "identical" - the customer ID numbers are different, therefore Oracle has to parse twice.
Because the only difference between these statements is the value used for the customer number, this application could be rewritten to use bind variables. In that case the SQL statement in question could be as follows:
SELECT * FROM CUSTOMER WHERE CUST_NBR = :X;
Oracle needs to parse thsi statement only once. The actual customer numbers would be supplied after parsing for each execution of the statment. Multiple, concurrently executing programs could chare the same copy of this SQL statement while at the same time supplying different customer number values.
In a multi-user application, situations such as the one described here are very common, and overall performance can be significantly improved by using bind varaibels, thereby reducing unnecessary parsing.
*********** END OF PARAGRAPH **************
Hope this helps to answer basic fundamentals of Bind Variables.
Hasmukh Daji
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
