Re: Bind Variables...

mark wrote:
>
> Thanks for being helpful as always Thomas
>
> But i was under the assumption that when we use binds, the optimizer
> makes poor decisions compared to when it has the exact value since the
> optimizer puts pre- determined values to selectivity for the bind
> columns leading to a good (low) selectivity for the entire query.
>
> From the little i know, different databases put an arbitary value for
> selectivity of 0.2 - 0.3 for the bind columns so as to favor the use
> of indexes . So if we use the bind when more than 15% of the rows of
> the table get retrieved, wont using the index might actually degrade
> performance. ( The 15% is the thumb rule, not always true). So wont
> the bind actually decrease performance if a large part of the table is
> retrieved?.
>
> My brain is usually out for lunch and i may be totally wrong on this.
>
> Pardon me for one small question out of this topic . Does the Ordering
> of columns matter in "Create Index". From the small test i conducted,
> it does not seem to matter. Celko says it may matter (Sql for
> Smarties). The other experts seem to be divided on this.
>
> Thomas Kyte <tkyte_at_us.oracle.com> wrote in message news:<9sa2jc01oe4_at_drn.newsguy.com>...
> > In article <9s9lm4$edf$1_at_innbox.cnet.com>, "Jack says...
> > >
> > >I have been debating the answer for this question.... but not really into a
> > >solution,,,,
> > >
> > >When to and When not to use bind variables???
> > >
> >
> > 99.999999% of the time -- use bind variables.
> >
> > In a datawarehouse, where you have computed histograms and have very skewed data
> > -- skip the bind variable on that column -- sometimes.
> >
> > >When to and When not to placeholder columns(&) ?/
> > >
> >
> > only when forced to.
> >
> > >Thanks
> > >Jack....
> > >
> > >
> > >

Oracle assumes an even distribution with bind variables when determining "how much" of a table will be read given a particular value. Whilst this conceivably could lead to poor access paths, you would expect this only to be case when the data in the relevants columns is skewed, and people are querying on the particular skewed elements.

hth
connor

-- 
==============================
Connor McDonald

http://www.oracledba.co.uk

"Some days you're the pigeon, some days you're the statue..."