Home » SQL & PL/SQL » SQL & PL/SQL » Semi Join Issue (Oracle 10g)
Semi Join Issue [message #442684] Tue, 09 February 2010 23:35 Go to next message
ram anand
Messages: 244
Registered: February 2008
Location: india
Senior Member
Hi all,

We came across a semi join issue in our porduction environment , A query with EXISTS clause gave us cartesian product ,we found that the issue is from that EXISTS clause condition only but in that query all the condition were correct,so confirming with oracle support its an Oracle BUG
for that fix we have just altered the _cost_equality_semi_join ,
can you guys tells for what purpose we were

alter session set "_cost_equality_semi_join"=false;


If the parameter is false the query executes without any issue but if its true then the issue occurs ,so pls help on this

Thanks in Advance,
Re: Semi Join Issue [message #442688 is a reply to message #442684] Wed, 10 February 2010 00:07 Go to previous messageGo to next message
rahulvb
Messages: 924
Registered: October 2009
Location: Somewhere Near Equator.
Senior Member
Seems nice and interesting Bug , can you please give the query and its results

Hidden parameters
Re: Semi Join Issue [message #442695 is a reply to message #442684] Wed, 10 February 2010 00:22 Go to previous messageGo to next message
Michel Cadot
Messages: 68733
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Why don't you ask Oracle the purpose of this parameter (and don't rely on dba-oracle site which by the way say nothing about the parameter)?
Only Oracle really knows what it does.

Regards
Michel
Re: Semi Join Issue [message #442705 is a reply to message #442695] Wed, 10 February 2010 01:06 Go to previous messageGo to next message
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Michel Cadot wrote on Wed, 10 February 2010 17:22
Only Oracle really knows what it does.


That's true, but it doesn't stop us taking a wild stab in the dark.

For instance, if I were to guess - which I am doing - I would say that this parameter permits the optimiser to generate a statistics-based cost for transforming EXISTS sub-queries that are correlated to the outer-query with equals clauses. The alternative is that CBO assigns a default cost, or does not consider such a plan unless hinted.

It seems likely that there is a bug in this cost calculation that - when used - results in an incorrect transformation of the query.

Ross Leishman
Re: Semi Join Issue [message #442740 is a reply to message #442684] Wed, 10 February 2010 05:52 Go to previous messageGo to next message
Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA
Senior Member
That would be my guess too.

Kevin
Re: Semi Join Issue [message #442863 is a reply to message #442688] Thu, 11 February 2010 00:47 Go to previous message
ram anand
Messages: 244
Registered: February 2008
Location: india
Senior Member
Hi,

Sorry for my late reply since its Confidential i am not suppose to deliver the Query, so sorry guys
Previous Topic: How to fetch data from different database using single select statement
Next Topic: Forward dependencies of a Database object
Goto Forum:
  


Current Time: Fri Feb 07 07:41:05 CST 2025