Re: Merge Join Cartesian

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