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

From: K Gopalakrishnan <kaygopal_at_yahoo.com>
Date: Fri, 30 Jul 2004 08:33:32 -0700 (PDT)
Message-ID: <20040730153332.80964.qmail@web41501.mail.yahoo.com>

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_at_freelists.org 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
-----------------------------------------------------------------

Received on Fri Jul 30 2004 - 10:31:32 CDT