Re: Cartesian joins

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