Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: How hints affect the CBO?
Well, I was thinking that way because it also make sense to allow people
to influence the CBO, but do not allow them to do stupid things, like forcing
full table scan on a table with several million rows to retrieve single row with
certain column being unique and present in where clause. If it was implemented
via cost tweaks, CBO would still have a way to ignore hints that will certainly
result in much worse performance. Having FULL hint as it is, aforementioned
use of it is unavoidable, and CBO will happily do FTS where it could do index
scan. On the other hand, it's very hard to identify a hint as really stupid having
only data available to CBO, so it simply assumes that the human being behind
that hint knows better. :)
At least I am no longer speculating about true CBO behavior with regard to hints and know for sure they are mandatory if valid.
-- Vladimir Zakharychev (bob@dpsp-yes.com) http://www.dpsp-yes.com Dynamic PSP(tm) - the first true RAD toolkit for Oracle-based internet applications. All opinions are mine and do not necessarily go in line with those of my employer. "Niall Litchfield" <n-litchfield_at_audit-commission.gov.uk> wrote in message news:3d1b1442$0$230$ed9e5944_at_reading.news.pipex.net...Received on Thu Jun 27 2002 - 09:29:13 CDT
> I was thinking some more about this, and this makes sense. The purpose of
> hints is to enable the optimiser to choose a *different* path to the one
> that it calculates as best. The behaviour that you originally postulated
> would probably make people swear at the optimiser even more than they
> currently do.
>
>
> --
> Niall Litchfield
> Oracle DBA
> Audit Commission UK
> *****************************************
> Please include version and platform
> and SQL where applicable
> It makes life easier and increases the
> likelihood of a good answer
>
> ******************************************
> "Vladimir M. Zakharychev" <bob_at_dpsp-yes.com> wrote in message
> news:aff0kg$s6h$1_at_babylon.agtel.net...
> > Ok, MetaLink note 69992.1 explicitly states that
> > "Correctly defined hints are DIRECTIVES to the optimizer to use a
> particular
> > access method or access type. They should not be ignored and should be
> > obeyed at all times by the optimizer."
> >
> > So my assumptions about CBO were incorrect after all.
> >
> > --
> > Vladimir Zakharychev (bob_at_dpsp-yes.com)
> http://www.dpsp-yes.com
> > Dynamic PSP(tm) - the first true RAD toolkit for Oracle-based internet
> applications.
> > All opinions are mine and do not necessarily go in line with those of my
> employer.
> >
> >
>
>