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

Home -> Community -> Usenet -> c.d.o.server -> Re: Help: Why does a query go from 4 secs to 2 hrs due to using NOT IN instead of NOT EXISTS

From: Joel Garry <joel-garry_at_home.com>
Date: 2 Jul 2004 11:55:33 -0700
Message-ID: <91884734.0407021055.479b3152@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?

--
@home.com is bogus.
Watch that patent law: http://kwiki.ffii.org/NlVote040701En

Received on Fri Jul 02 2004 - 13:55:33 CDT