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)
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 //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
Other related posts:
