Re: Merge Join Cartesian

Start with asking Oracle why it thinks a hash join is not worth doing: hint at it and check where the additional costs come from. Then see where the numbers are going wrong.

Assuming that a Cartesian join really isn’t involved in the most optimal execution plan. Eg for star schemas, they’re usually necessary as you can only join from one row source to one row source.

Hope that helps,
Andy

On Wed, 10 Jan 2024 at 22:43, Mladen Gogala <gogala.mladen_at_gmail.com> wrote:

> On 1/10/24 16:29, Amit Saroha 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
>
> What has happened to the last statistics run? CBO might have decided that
> your statistics is stale and refused to use the stats. You can check in
> DBA_TAB_STATISTICS.
>
>
> --
> Mladen Gogala
> Database Consultant
> Tel: (347) 321-1217https://dbwhisperer.wordpress.com
>
>

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