值得反复研读的表连接之CARTESIAN JOIN方式

两表关联却无关联条件必会产生笛卡尔积。

[oracle@MaxwellDBA ~]$ sqlplus sys/sys as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Sun Oct 23 10:50:01 2022
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

sys@cdb$root:orclcdb> ALTER SESSION SET CONTAINER=ORCLPDB1;

Session altered.

sys@cdb$root:orclcdb> conn SCOTT/TIGER@ORCLPDB1;
Connected.
scott@orclpdb1:orclcdb>
scott@orclpdb1:orclcdb> set autot trace;
scott@orclpdb1:orclcdb>
scott@orclpdb1:orclcdb> select * from emp,dept;

56 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 2034389985

-----------------------------------------------------------------------------
| Id  | Operation            | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |      |    56 |  3248 |    10   (0)| 00:00:01 |
|   1 |  MERGE JOIN CARTESIAN|      |    56 |  3248 |    10   (0)| 00:00:01 |
|   2 |   TABLE ACCESS FULL  | DEPT |     4 |    80 |     3   (0)| 00:00:01 |
|   3 |   BUFFER SORT        |      |    14 |   532 |     7   (0)| 00:00:01 |
|   4 |    TABLE ACCESS FULL | EMP  |    14 |   532 |     2   (0)| 00:00:01 |
-----------------------------------------------------------------------------

Statistics
----------------------------------------------------------
         97  recursive calls
          0  db block gets
        218  consistent gets
         30  physical reads
          0  redo size
       4393  bytes sent via SQL*Net to client
        417  bytes received via SQL*Net from client
          5  SQL*Net roundtrips to/from client
         16  sorts (memory)
          0  sorts (disk)
         56  rows processed

scott@orclpdb1:orclcdb>

执行计划中MERGE JOIN CARTESIAN表明为笛卡尔连接。笛卡尔会返回两个表的乘积。

DEPT 4 rows 

EMP 14 rows

两表的乘积则为56 rows

BUFFER SORT 表示对其中以表进行排序。

在多表关联的时候，两个表没有直接关联条件，但是优化器错误地把某个表返回的Rows算为1行（注意必须是1行），这个时候也可能发生笛卡尔儿连接。

如果两表有直接关联条件，无法控制两个表进行笛卡尔连接。

如果两表没有直接关联条件，可将两个表依次放在from后面并且添加HINT：ordered,就可以使两表进行笛卡尔积关联

scott@orclpdb1:orclcdb> set autot trace
scott@orclpdb1:orclcdb>
scott@orclpdb1:orclcdb> select /*+ ordered */
  2  a.ename, a.sal, a.deptno, b.dname,c.grade
from dept b, salgrade c, emp a
  4  where a.deptno = b.deptno
  5  and a.sal between c.losal and c.hisal;

14 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 2197699399

----------------------------------------------------------------------------------
| Id  | Operation             | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |          |    42 |  1512 |    13   (0)| 00:00:01 |
|*  1 |  HASH JOIN            |          |    42 |  1512 |    13   (0)| 00:00:01 |
|   2 |   MERGE JOIN CARTESIAN|          |    20 |   460 |    10   (0)| 00:00:01 |
|   3 |    TABLE ACCESS FULL  | DEPT     |     4 |    52 |     3   (0)| 00:00:01 |
|   4 |    BUFFER SORT        |          |     5 |    50 |     7   (0)| 00:00:01 |
|   5 |     TABLE ACCESS FULL | SALGRADE |     5 |    50 |     2   (0)| 00:00:01 |
|   6 |   TABLE ACCESS FULL   | EMP      |    14 |   182 |     3   (0)| 00:00:01 |
----------------------------------------------------------------------------------

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

   1 - access("A"."DEPTNO"="B"."DEPTNO")
       filter("A"."SAL"<="C"."HISAL" AND "A"."SAL">="C"."LOSAL")

Statistics
----------------------------------------------------------
         52  recursive calls
          0  db block gets
         62  consistent gets
          8  physical reads
          0  redo size
       1263  bytes sent via SQL*Net to client
        741  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          5  sorts (memory)
          0  sorts (disk)
         14  rows processed

scott@orclpdb1:orclcdb>

DEPT 和 SALGRADE没有直接关联条件，HINT:ordered表示根据SQL语句中from后面表的顺序依次关联。故DEPT与SALGRADE只能进行笛卡尔连接。

Q:当执行计划中有笛卡尔连接应该怎么优化？

Step1: 检查表与表之间是否存在关联条件，如果没有，可咨询开发与业务人员为何没有关联条件。是否是为了满足业务的需求的某种原因而故意不写关联条件。

Step2 : 检查离笛卡尔连接最近的表是否真的返回1行数据，如果返回行数真的只有1行，那么走笛卡尔连接是没有问题的。如果超过一行，说明估算错误，同时要纠正估算错误的Rows。纠正错误的Rows之后，优化器就不会走笛卡尔连接了。

同时，可以使用HINT/*+ opt_param('_optimizer_mjc_enabled','false')*/ 禁止笛卡尔连接。