Semi Join Issue [message #442684] |
Tue, 09 February 2010 23:35  |
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 #442705 is a reply to message #442695] |
Wed, 10 February 2010 01:06   |
rleishman
Messages: 3728 Registered: October 2005 Location: Melbourne, Australia
|
Senior Member |
|
|
Michel Cadot wrote on Wed, 10 February 2010 17:22Only 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
|
|
|
|
|