两表关联却无关联条件必会产生笛卡尔积。
[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.0Copyright (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.0sys@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 processedscott@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 processedscott@orclpdb1:orclcdb>
DEPT 和 SALGRADE没有直接关联条件,HINT:ordered表示根据SQL语句中from后面表的顺序依次关联。故DEPT与SALGRADE只能进行笛卡尔连接。
Q:当执行计划中有笛卡尔连接应该怎么优化?
Step1: 检查表与表之间是否存在关联条件,如果没有,可咨询开发与业务人员为何没有关联条件。是否是为了满足业务的需求的某种原因而故意不写关联条件。
Step2 : 检查离笛卡尔连接最近的表是否真的返回1行数据,如果返回行数真的只有1行,那么走笛卡尔连接是没有问题的。如果超过一行,说明估算错误,同时要纠正估算错误的Rows。纠正错误的Rows之后,优化器就不会走笛卡尔连接了。
同时,可以使用HINT/*+ opt_param('_optimizer_mjc_enabled','false')*/ 禁止笛卡尔连接。