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

I have the values like below, but still it is going for NL_JOIN.

SQL> show parameter hash
 
NAME                                 TYPE    VALUE
------------------------------------ ------- ------------------------------
hash_area_size                       integer 2000000
hash_join_enabled                    boolean TRUE
hash_multiblock_io_count             integer 0
SQL> show parameter join
 
NAME                                 TYPE    VALUE
------------------------------------ ------- ------------------------------
always_anti_join                     string  HASH
always_semi_join                     string  HASH
hash_join_enabled                    boolean TRUE
SQL> 



On Wed, 20 Oct 2004 17:18:38 -0300 (ART), Antonio Belloni
<toni_belloni@xxxxxxxxxxxx> wrote:
> 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.
--
http://www.freelists.org/webpage/oracle-l

Other related posts: