Re: Merge Join Cartesian

Hi,

I once had a customer where Merge Join Cartesian happened too often by general judgement.
You can supress Merge Join Cartesian by setting an hidden parameter. I tested it once being curios. As I suspected it did not really help. We got strange nested loop joins instead.
The real issue is often an wrong estimate of one row that makes MJC look cheap.

It took us a while, but we could figure out the underlying issue for wrong cardinality estimates. (It was partitioning that did not go along with the application.) We got it fixed, that way not only getting rid of the majority of MJC but also of many other issues. Quite often there is no easy answer.

Thanks

Lothar

Am 11.01.2024 um 10:42 schrieb Jonathan Lewis:
>
> As Dominic pointed out, it's because you have a number of tables where
> the arithmetic for the available predicates gives a cardinality of 1,
> which makes the means the optimizer (often) promotes their status to
> "single row tables" and puts them at the start of the initial join
> order - even when there's no join condition between the tables.
>
> It's pointless to guess why this might be happening to "a lot of
> queries" in your application since you haven't supplied an example of
> a query and plan demonstrating the pattern, and you haven't told us
> the version of Oracle; but if it's an older version of Oracle AND it's
> causing performance problems then it may be a side effect of
> multi-column predicates on correlated columns where you need to help
> the optimizer by creating extended (viz: column group) statistics.
>
> Regards
> Jonathan Lewis
>
>
>
>
>
> On Wed, 10 Jan 2024 at 21:31, Amit Saroha <eramitsaroha_at_gmail.com> wrote:
>
> Hi All,
>
> I've noticed a lot of queries in our environment that use Merge
> Join Cartesian instead of Hash Join or Nested Loop Joins, which
> seems unusual to me. Could you please let me know if you've
> noticed anything similar and what the cause might be?
>
> Thank you in advance for your assistance and support.
>
> Best Regards,
> AMIT
>

--
http://www.freelists.org/webpage/oracle-l