Re: Help: Why does a query go from 4 secs to 2 hrs due to using NOT IN instead of NOT EXISTS

"Niall Litchfield" <niall.litchfield_at_dial.pipex.com> wrote in message news:<40e82ee0$0$6441$cc9e4d1f_at_news-text.dial.pipex.com>...
> "Joel Garry" <joel-garry_at_home.com> wrote in message
> news:91884734.0407021055.479b3152_at_posting.google.com...
> > Sybrand Bakker <sybrandb_at_hccnet.nl> wrote in message
> news:<09q9e09gjo3nfg0h06s31ljk9709gcu0p0_at_4ax.com>...
> >
> > > In CBO, using the /*+HASH_AJ*/ hint on not in subqueries should help.
> > > Maybe you should also very optimizer_index_caching and
> > > optimizer_index_cost_adj. Their default values will result in weird
> > > execution plans. Optimizer_index_caching should be set to your cache
> > > hit ratio.
> >
> > I'm not sure I quite see why a parameter for costing index scans
> > should be set to a hit ratio of a buffer that has both index and data
> > in it. How would such a ratio relate to the percentage of index
> > blocks in the cache? Or am I missing a joke?
>
> The rationale goes like this.
>
> O_I_C represents to the optimizer the likelihood of finding an index block
> in memory instead of on disk. Thus the default of 0 tells the optimizer that
> every index access will be a physical read. As we are dealing with index
> accesses on a system wide level we have no real way of knowing what the
> actual likelihood is for indexes on average, but the BCHR would be a good
> start.

OK, that makes some sense. I can see where it would be useful as such a starting point if it is applied to a system where the BCHR happens to reflect the most used indices (or has been "tuned" to get a high BCHR :). And whatever it is, it is not likely to be worse than the default. I kind of like the more stark rule of thumb in http://www.evdbt.com/SearchIntelligenceCBO.doc .

jg

--
@home.com is bogus.
Larry "Atlas" Ellison, the Objectivists must love this:
http://www.signonsandiego.com/uniontrib/20040706/news_1b6oracle.html