Re: Cartesian joins

From: Daniel Fink <"Daniel>
Date: Wed, 3 Apr 2019 08:25:34 -0600
Message-ID: <CAL2aWL7sWEz_Z2YwCpxYmLw07YLj0He0qeLgZN6mcyWOBe7Kzg_at_mail.gmail.com>



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>

--
http://www.freelists.org/webpage/oracle-l
Received on Wed Apr 03 2019 - 16:25:34 CEST

Original text of this message