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> 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> 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> 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
>
> [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
>
>

--

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