2

Reading "Pro Oracle SQL" by Karen Morten, page 9 "FROM clause" says

Joins are processed in the following order

  1. Cross Join
  2. Inner join
  3. Outer join

So does Oracle always create a Cartesian product of the two tables involved in a join whether it is inner join or outer join?

Table A have values 1 to 10 (unique) and table B have values (2,4,6,8)

Inner join:

select a.a1, b.b1 from a,b where a.a1 = b.b1;

Outer join:

select a.a1, b.b1 from a,b where a.a1 = (+) b.b1;

Form a result based on

  • Create a Cartesian product of 10*4 and then show records based on the criteria (outer or equi)
  • Do not create a Cartesian product, just create a result set based on join predicate (equi join only not sure whether this can be done for outer join, I think Cartesian product approach suites for outer join)
Alex Poole
  • 183,384
  • 11
  • 179
  • 318
Nocofoolme
  • 91
  • 8
  • asking for an explanation? – Alex Salauyou Jun 25 '18 at 09:08
  • 2
    I think that quote is perhaps talking about what happens when there are *multiple* joins of different types *in the same statement*. I'm not sure it's right anyway though. [See the documentation for an explanation of how joins are processed](https://docs.oracle.com/en/database/oracle/oracle-database/12.2/tgsql/joins.html#GUID-BD96F1B4-76D4-43DF-98B6-D07F46838C4A). – Alex Poole Jun 25 '18 at 09:34
  • 3
    I would say that the statement "Joins are processed in the following order: 1,Cross Join 2,Inner join 3,Outer join" is incorrect; Oracle's optimizer determines the order in which tables should be joined according to what it thinks is best. That might be to process the cartesion/cross join last, first or somewhere in the middle. – Boneist Jun 25 '18 at 09:37
  • 1
    If the book is explaining joins using comma syntax, then get another book. You should be learning proper `JOIN` syntax -- it is more powerful and has been in the standard for decades. – Gordon Linoff Jun 25 '18 at 11:08
  • @GordonLinoff - No it's not the syntax the author had used, it's me who find it difficult with JOIN syntax. – Nocofoolme Jun 25 '18 at 14:31
  • btw that should be `where a.a1 (+) = b.b1`. – William Robertson Jun 25 '18 at 22:37

1 Answers1

1

No, it doesn't build a cartesian product of the two tables for a inner or outer join.

The database uses a Cartesian join when one or more of the tables does not have any join conditions to any other tables in the statement." Database SQL Tuning Guide

So, in your example, it does a cartesian join only if the where clause is empty:

EXPLAIN PLAN FOR
SELECT a.a1, b.b1 FROM a,b;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
-----------------------------------------------------------------------------
| Id  | Operation            | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |      |    40 |   240 |    10   (0)| 00:00:01 |
|   1 |  MERGE JOIN CARTESIAN|      |    40 |   240 |    10   (0)| 00:00:01 |
|   2 |   TABLE ACCESS FULL  | B    |     4 |    12 |     3   (0)| 00:00:01 |
|   3 |   BUFFER SORT        |      |    10 |    30 |     7   (0)| 00:00:01 |
|   4 |    TABLE ACCESS FULL | A    |    10 |    30 |     2   (0)| 00:00:01 |
-----------------------------------------------------------------------------

    SELECT a.a1, b.b1 FROM a,b;

It normally uses one of three join methods, which are explained in the same document.

In your case, it's using a "hash join" for the inner join:

EXPLAIN PLAN FOR
SELECT a.a1, b.b1 FROM a,b WHERE a.a1 = b.b1;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     4 |    24 |     6   (0)| 00:00:01 |
|*  1 |  HASH JOIN         |      |     4 |    24 |     6   (0)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| B    |     4 |    12 |     3   (0)| 00:00:01 |
|   3 |   TABLE ACCESS FULL| A    |    10 |    30 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("A"."A1"="B"."B1")

and a "hash join outer" for your outer join:

EXPLAIN PLAN FOR
SELECT a.a1, b.b1 FROM a,b WHERE a.a1 = b.b1(+);
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |    10 |    60 |     6   (0)| 00:00:01 |
|*  1 |  HASH JOIN OUTER   |      |    10 |    60 |     6   (0)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| A    |    10 |    30 |     3   (0)| 00:00:01 |
|   3 |   TABLE ACCESS FULL| B    |     4 |    12 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("A"."A1"="B"."B1"(+))
wolφi
  • 8,091
  • 2
  • 35
  • 64
  • Ok,For inner and outer joins it doesn't need a cartesian product. Got it.So for a full outer join, I thought it should be creating a cartesian product. Reason, A Cartesian product can be used to match both left and right outer join conditions. My question is except for a case where there is no join condition, under what conditions cartesian product will be used? – Nocofoolme Jun 26 '18 at 08:07
  • There is a technical difference between full outer join and cartesian join. Just checked it with a join of two tables with 3 million rows each: join 6.2 seconds for 2.8 million rows, full outer join: 7.4 seconds for 4.9 million rows. Cross join still running after a couple of minutes. – wolφi Jun 26 '18 at 14:13
  • To your specific question, please check [When the Optimizer Considers Cartesian Joins](https://docs.oracle.com/database/121/TGSQL/tgsql_join.htm#TGSQL94986). Basically, it uses cartesian joins, 1) if you forget a where clause, 2) if you force it to or 3) if it is faster, for instance if you join two small tables to a big table, it might crossjoin both small tables. – wolφi Jun 26 '18 at 14:17