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>
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> 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> 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
[image: Lifecycle Metrics Benchmark]
<http://signatures.returnpath.com/uc/5b731f4d558a8600a895089c>
[image: Powered by Sigstr]
<http://signatures.returnpath.com/uc/5b731f4d558a8600a895089c/watermark>
--
Mladen Gogala
Database Consultant
Tel: (347) 321-1217
