Re: Cost based optimiser (on 8.1.7.4)

Hi Neil,

Another point is that the CBO will not like (no pun intended honest) LIKE bind variable as this implies an unbounded select clause, which 'could' return many values (eg. LIKE '%'). The = is specific and based on the statistics can make an accurate estimate (assuming even distribution which all it can assume). For a unbounded condition, the optimizer assumes 'a large percentage of rows'.

Hence my friend your execution plan ...

Cheers

Richard

"Neil" <hello_at_thirdchimp.net> wrote in message news:9505a41d.0208290634.38dc8ada_at_posting.google.com...
> Sybrand Bakker <postbus_at_sybrandb.demon-verwijderdit.nl> wrote in message
news:<1r6qmu0p4j1qkn41gaknikiu7g19vqahsv_at_4ax.com>...
> >
> > Bind variables: this means CBO ignores histograms (but you might not
> > have them at all you don't specify), and assumes an even distribution
> > of the data.
> > RBO, of course, doesn't assume anything about distribution (so it
> > *definitely doesn't have a much brighter outlook), it just uses an
> > index wherever it can.
> > You could try playing around with optimizer_index_cost_adj and similar
> > parameters, apparently full table scans are considered 'cheap'
> > Generically, I would however start with the FIRST_ROWS and the
> > ORDERED, and of course there is event 10053 which will dump the
> > rationale of CBO in a trace file. The Jonathan Lewis book has a
> > description of the output.
> >
> > Hth
> >
> >
> > Sybrand Bakker, Senior Oracle DBA
> >
> > To reply remove -verwijderdit from my e-mail address
>
> Thanks for your help Sybrand
Received on Thu Aug 29 2002 - 20:45:56 CDT