Re: How hints affect the CBO?

Niall,

thanks for responce and doc link.

> > That is, no matter how inneffective a query with FULL hint is, CBO will
> > always do full scan even if index access yelds better plan, because it is
> > explicitly instructed to do so and this is valid access path.
>
> In this particular case then this is correct (at least on 9.2 but I believe
> it applies to 817 etc as well. )
>

Well, FULL hint was just a bold example. What I was really interested in is if all other access path hints exhibit the same behavior if they specify valid access path, that is, they will necessarily be honored by the CBO if the path is still valid (for example, INDEX(t idx) and idx exists and all stats are there). Or if there is a clear example of otherwise. I didn't manage to create one, but I still think this is not impossible unless valid hints are treated as direct orders which CBO must obey (and if it doesn't then this is either not a valid hint or a bug in CBO).

-- 
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.