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@xxxxxxxxxxxxx <oracle-l-bounce@xxxxxxxxxxxxx> on behalf
of Ram Raman <veeeraman@xxxxxxxxx>
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@xxxxxxxxx<mailto:gogala.mladen@xxxxxxxxx>> 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@xxxxxxxxx<mailto:veeeraman@xxxxxxxxx>> 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@xxxxxxxxx<mailto:oralrnr@xxxxxxxxx>> 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@xxxxxxxxxxxxxx<mailto:daniel.fink@xxxxxxxxxxxxxx>
[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
--
--
//www.freelists.org/webpage/oracle-l
