Re: Cartesian joins

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Thu, 4 Apr 2019 07:05:45 +0000
Message-ID: <LO2P265MB0415CE504B402870E15E30C4A5500_at_LO2P265MB0415.GBRP265.PROD.OUTLOOK.COM>

Your example is a case where your join has disappeared because of transitive closure, and Oracle has detected that every row in rowsource1 will join to every row in rowsource2. See: https://jonathanlewis.wordpress.com/2006/12/13/cartesian-merge-join/

Regards
Jonathan Lewis

---

From: oracle-l-bounce_at_freelists.org <oracle-l-bounce_at_freelists.org> on behalf of Ram Raman <veeeraman_at_gmail.com> Sent: 04 April 2019 00:56
To: Mladen Gogala
Cc: ORACLE-L
Subject: Re: Cartesian joins

I did some testing by creating two tables, one with 1000 rows and another with 10,000 and later 70K rows, the optimizer likes the cartesian join over and over for the test cases. Something I did not copy in the link is that I did "insert into cj2 select * from cj2" a few times and committed, collected stats and re did the experiment. For upto 2 rows returned the optimizer went with Cartesian joins. When the number of rows returned went to 32 it went to Hash joins. It also went to HASH joins if I made the cj1 index invisible or forced USE_HASH on the joins. What is annoying is that i did not find the cost column in the plans, not sure what I missed. It will be nice to be able to compare costs.

https://drive.google.com/file/d/1CM5nk4nbKE7Lptsimb9ns_jS_WtshaOF/view?usp=sharing

On Wed, Apr 3, 2019 at 10:20 AM Mladen Gogala <gogala.mladen_at_gmail.com<mailto:gogala.mladen_at_gmail.com>> wrote:

Hi Dan,

Cartesian join is still a big red flag because it may mean at least one one full table scan. Cartesian join is a Cartesian product of the row sources involved. I am not quite certain about the optimization and what exactly is being optimized by a Cartesian join.

Regards

On 4/3/19 10:25 AM, Daniel Fink (Redacted sender daniel.fink for DMARC) wrote: Yes - there is an optimization process that will perform a cartesian join IF one of the row sources will return a single row. I don't recall when it was introduced (11gR2?). Seeing 'CARTESIAN' in the query plan used to be a big ol' red flag...now it is an indication that you should take a closer look at the row sources in the join.

On Tue, Apr 2, 2019 at 2:26 PM Ram Raman <veeeraman_at_gmail.com<mailto:veeeraman_at_gmail.com>> wrote: I am not a big expert on joins, but I think Oracle chooses Cartesian when it thinks one of the row sources is going to return only one row.

On Sat, Mar 30, 2019 at 3:08 PM Orlando L <oralrnr_at_gmail.com<mailto:oralrnr_at_gmail.com>> wrote: List

When is it OK to do Cartesian joins?

1. It looks like during the star transformation while joining 2 or more small result sets from dimension tables?
2. In an ordinary join, when one of the row sources is estimated to be 1 row with the other row source be several thousand? In this case, the Cartesian will be just 1*no of rows in the other table.

Any explanation is helpful. thanks

Orlando.

--

--

Daniel Fink
Sr. Database Administrator | Return Path m | (303) 808 3282
daniel.fink_at_returnpath.com<mailto:daniel.fink_at_returnpath.com>

[Lifecycle Metrics Benchmark]
<http://signatures.returnpath.com/uc/5b731f4d558a8600a895089c> [Powered by Sigstr]<http://signatures.returnpath.com/uc/5b731f4d558a8600a895089c/watermark>

--

Mladen Gogala
Database Consultant
Tel: (347) 321-1217

--

--

http://www.freelists.org/webpage/oracle-l Received on Thu Apr 04 2019 - 09:05:45 CEST