Re: Is there any way to disable hash semi-join in 9i?

Prasad:

Yet another semi-join killer. Have you tried forcing a NL join (hoping
that NL semi-join is slightly better!). You can specify the init.ora
parameter _always_semi_join to nested_loops if you want to use the NL
semi-join.  The other option is to completely disable the semi-join.
This can be done by setting the parameter to 'off'. 

You can set this in session-level and try the query. If that works, you
can use a logon-trigger to set this parameter before running the query
and turn it back after the query completes. In this case, you are not
using this parameter for instance-wide.

Pls let me know how it goes, otherwise you can send me the 10053 trace
and I can tell you why it is using semi-join ;)


> I would like to know if there is any way to disable hash semi join
> (do not
> want to disable hash joins) either at the instance level (by
> specifying any
> parameters in init.ora) or at the query level by specifying hint. I
> have
> gone thru the documentation but did not find any way to disable it.  
> There
> is a hidden parameter '_always_semi_join' and has the value 'CHOOSE'.
> I
> tried to disable it by specifying various values (FALSE, NONE etc) in
> init.ora but no success. Also, even if it works, specifying this


=====
Have a nice day !!
------------------------------------------------------------
Best Regards,
K Gopalakrishnan,
Co-Author: Oracle Wait Interface: Oracle Press 2004.
http://www.amazon.com/exec/obidos/tg/detail/-/007222729X/


                
__________________________________
Do you Yahoo!?
Yahoo! Mail - 50x more storage than other providers!
http://promotions.yahoo.com/new_mail
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to:  oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------

Other related posts: