Re: Bind Variables...

Bind variables

Advantages
If you use bind variables, then the Sql statement gets cached . So the next time you execute this , Oracle does not parse the query once again. Use bind variables when the same Sql Statement gets executed several times and response times have to be fast.

Disadvantages
I said earlier that the response time will be faster the second time. This may not always be true

Select * from some_table where Some_column = :1

Oracle will not know the value of :1 when it determines the best way to execute the query. Problem here is that the Oracle optimizer does not know the "Full details" when it charts out the execution plan. This may cause an execution plan that is usually worse than if it knew the value of :1. Usually the cost based optimizer places an arbitary selectivity to favor the use of an index.

As a good enough thumb rule, if you have a statement which executes once or very few times and response times are good, avoid bind variables.

For repetitive operations, especially inserts, bind variables are an absolute must.

I do not know what placeholders are . sorry.

Additional corrections welcomed :-)

"Jack Dawson" <srik_at_cnet.com> wrote in message news:<9s9lm4$edf$1_at_innbox.cnet.com>...
> I have been debating the answer for this question.... but not really into a
> solution,,,,
>
> When to and When not to use bind variables???
>
> When to and When not to placeholder columns(&) ?/
>
> Thanks
> Jack....
Received on Wed Nov 07 2001 - 00:10:29 CST