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

  • From: K Gopalakrishnan <kaygopal@xxxxxxxxx>
  • To: oracle-l@xxxxxxxxxxxxx
  • Date: Fri, 30 Jul 2004 08:33:32 -0700 (PDT)


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.

Do you Yahoo!?
Yahoo! Mail - 50x more storage than other providers!
Please see the official ORACLE-L FAQ:
To unsubscribe send email to:  oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
Archives are at //
FAQ is at //

Other related posts: