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@xxxxxxxxx> 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
