Ram, "Cartesian join" is what mathematicians call "Cartesian product".
Cartesian product of two sets, A and B is a set of all tuples (a,b)
where a is an element of A and b is an element of B. Basically, it's
join that contains every possible combination of rows (a,b) where a is a
row of table A and b is a row of table B. Similarity between the
elementary set theory and and SQL is more than skin deep. I would
recommend the book of applied mathematics for database professionals by
the late Lex de Haan to everybody. It contains the basic probability and
set theory and is well written.
Oracle usually chooses Cartesian join when there is no join condition
specified. Cartesian join is named after the French mathematician Rene
Descartes - Cartesius and his famous saying: I join, therefore I am.
On 4/2/19 4:25 PM, Ram Raman 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.
--