Re: Why the optimizer is not choosing the best plan?

Hi,

If you want that Oracle consider hash joins , you
should set the following parameters in the init.ora:

HASH_JOIN_ENABLED = TRUE
HASH_AREA_SIZE = n , where n is the amount of memory
allocated for hash joins.

Also , consider setting the following parameters:
ALWAYS_SEMI_JOIN = HASH
ALWAYS_ANTI_JOIN = HASH

So , semi and anti join operations will be using hash
join.

For more information about the parameters and their
valid values , see "Oracle Reference Guide" in
http://tahiti.oracle.com

Regards,
Antonio Belloni

> 
> I have 4 tables join query(all tables statistics are
> upto date) that
> is working fine if it goes for HASH_JOIN.  But
> without HINT it is
> always doing NL_JOIN and  takes about 50 secs to
> execute.
> 
> However using ORDERED hint it is going for HASH_JOIN
> and the response
> time got reduced to less than 5 secs, but we don't
> prefer to implement
> HINT based solution
> 
> Why the optimizer is not choosing the best execution
> plan(In this case
> hash_join rather than NL_join)?
> 



        
        
                
_______________________________________________________ 
Yahoo! Acesso Grátis - Internet rápida e grátis. Instale o discador agora! 
http://br.acesso.yahoo.com/
--
http://www.freelists.org/webpage/oracle-l

Other related posts: