Hello Vikas,
As You said We should always make use of bind variables as it executes
faster as compare to the statements where we do not
make use of bind variables.
Q1) Can you please take a more specific example as how a statement can be
altered to make use of bind variable.
Q2) I made use of
I thought that bind variables were faster but you always have to ensure that
if you're accessing by data which may be heavily skewed and histograms would
usually help you may not want to use bind variables as they will disable the
use of histograms.
In saying that it doesn't look as though that
Vikas
I had a developer recently report that his program was actually a little
faster by not using bind variables. Once I stopped screaming I calmly
explained the following.
The issue isn't whether bind variables are faster or not, but rather what
SQL statements that don't use bind variables
I thought that bind variables were faster
but you always have to ensure that
if you're accessing by data which may be
heavily skewed and histograms would
usually help you may not want to use
bind variables as they will disable
the use of histograms.
What will happen if i don't use bind
Another issue is the ability of the CBO to use bind variables. Until
recently (9i, I think), the CBO would use a single execution plan for a
statement with bind variables, even if the data values of the bind variables
actually indicated that the plan was not optimal. For example, a query that
This is true in 8i. But in 9i, this has changed per Gaja. Even if bind
variables are used, it will use histograms if histograms are present.
Prakash
-Original Message-
Sent: Friday, September 06, 2002 10:23 AM
To: Multiple recipients of list ORACLE-L
I thought that bind variables were
Histograms are useless if the optimizer does not know the exact value in the
bind variable during a one pass execution plan (static execution plans). But
if the execution path could be delayed to a later phase (bind stage) then
probably the execution plan could be altered based on the value in
...But only on the first execution of a session. It's a bug.
Test (credit Jonathan Lewis):
0. Set up select c1, c2 from t1 where c1=:bind1 where different bind1
values would motivate different execution plans if we used literals.
E.g., insert only a few rows where c1=5, and thousands of rows
