Re: 9i query runs slower than 8i

On 22 Sep 2004 09:16:08 -0700, datavector_at_hotmail.com (Ken Chesak) wrote:

>The following query runs slower in 9i about 30 seconds than 8i about 15 seconds.
>Seems 8i is choosing a hash join and 9i is not.

That's not the whole story. The hash join is a result the fact there are 2 full table scans in this part of the query. Somehow in 8i Oracle can't use the index and in 9i it can. This might be the result of different init.ora parameters (optimizer_cost_adj, optimizer_index_caching, and db_file_multiblock_read_count spring to mind) and it might be the result of a different clustering_factor of the index, or inaccurate statistics etc.

--
Sybrand Bakker, Senior Oracle DBA