-
Notifications
You must be signed in to change notification settings - Fork 0
/
cbo_cartj
135 lines (101 loc) · 7.83 KB
/
cbo_cartj
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
两个表关联没有连接条件的时候就会产生笛卡尔笛卡儿积，这种表连接方式就叫笛卡尔笛卡儿(cartesian join)连接。
在测试账号scott中，运行如下SQL：
SQL> set autot trace
SQL> 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 | 8 (0)| 00:00:01 |
| 1 | MERGE JOIN CARTESIAN| | 56 | 3248 | 8 (0)| 00:00:01 |
| 2 | TABLE ACCESS FULL | DEPT | 4 | 80 | 3 (0)| 00:00:01 |
| 3 | BUFFER SORT | | 14 | 532 | 5 (0)| 00:00:01 |
| 4 | TABLE ACCESS FULL | EMP | 14 | 532 | 1 (0)| 00:00:01 |
-----------------------------------------------------------------------------
执行计划中MERGE JOIN CARTESIAN就表示笛卡尔连接。笛卡尔连接会返回两个表的乘积。DEPT有4行数据，EMP有14行数据，两个表进行笛卡尔连接之后会返回56行数据。笛卡尔连接会对两表中其中一个表进行排序，执行计划中的BUFFER SORT就表示排序。
在多表关联的时候，两个表没有直接关联条件，但是优化器错误的把某个表返回的Rows算为1行(注意必须是1行)，这个时候也可能发生笛卡尔连接。例如：
SQL> select * from table(dbms_xplan.display());
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------
Plan hash value: 710264295
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 |
| 1 | WINDOW SORT | | 1 |
|* 2 | TABLE ACCESS BY INDEX ROWID | F_AGT_GUARANTY_INFO_H | 1 |
| 3 | NESTED LOOPS | | 1 |
| 4 | NESTED LOOPS | | 1 |
| 5 | MERGE JOIN CARTESIAN | | 1 |
| 6 | TABLE ACCESS FULL | B_M_BUSINESS_CONTRACT | 1 |
| 7 | BUFFER SORT | | 61507 |
|* 8 | TABLE ACCESS FULL | F_AGT_GUARANTY_RELATIVE_H | 61507 |
| 9 | TABLE ACCESS BY INDEX ROWID| F_CONTRACT_RELATIVE | 1 |
|* 10 | INDEX UNIQUE SCAN | SYS_C0019578 | 1 |
|* 11 | INDEX RANGE SCAN | SYS_C005707 | 1 |
-----------------------------------------------------------------------------
执行计划中Id=6的表和Id=8的表就是进行笛卡尔连接的。
在这个执行计划中，为什么优化器会选择笛卡尔积连接呢？
因为Id=6这个表返回的Rows被优化器错误的估算为1行，优化器认为1行的表与任意大小的表进行笛卡尔关联，数据也不会翻翻，优化器认为这是安全的。所以这里优化器选择了笛卡尔连接。
Id=6这步是全表扫描，并且没过滤条件(因为没有*)，优化器认为它只返回1行。各位读者请思考，全表扫描返回1行，并且无过滤条件，这个可能吗？难道表里面真的就只有1行数据？这不符合常识。
那么显然是Id=6的表没有收集统计信息，导致优化器默认的把该表算为1行(当时数据库没开启动态采样)。下面是上述执行计划的SQL语句：
SELECT b.agmt_id,
b.corp_org,
b.cur_cd,
b.businesstype,
c.object_no,
c.guaranty_crsum,
row_number() over(PARTITION BY b.agmt_id, b.corp_org, c.object_no ORDER BY b.agmt_id, b.corp_org, c.object_no) row_no
FROM b_m_business_contract b, --合同表
dwf.f_contract_relative c, --合同关联表
dwf.f_agt_guaranty_relative_h r, --业务合同、担保合同与担保物关联表
dwf.f_agt_guaranty_info_h g --担保物信息表
WHERE b.corp_org = c.corp_org
AND b.agmt_id = c.contract_seqno --业务合同号
AND c.object_type = 'GuarantyContract'
AND r.start_dt <= DATE '2012-09-17' /*当天日期*/
AND r.end_dt > DATE '2012-09-17' /*当天日期*/
AND c.contract_seqno = r.object_no --业务合同号
AND c.object_no = r.guaranty_no --担保合同编号
AND c.corp_org = r.corp_org --企业法人编码
AND r.object_type = 'BusinessContract'
AND r.agmt_id = g.agmt_id --担保物编号
AND r.corp_org = g.corp_org --企业法人编码
AND g.start_dt <= DATE '2012-09-17' /*当天日期*/
AND g.end_dt > DATE '2012-09-17' /*当天日期*/
AND g.guarantytype = '020010' --质押存款
执行计划中进行笛卡尔关联的表就是b和r，在SQL语句中b和r没有直接关联条件。
如果两个表有直接关联条件，无法控制两个表进行笛卡尔连接。
如果两个表没有直接关联条件，在编写SQL的时候将两个表依次放在from后面，并且添加HINT：ordered，就可以使两个表进行笛卡尔积关联：
SQL> select /*+ ordered */
2 a.ename, a.sal, a.deptno, b.dname, c.grade
3 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 | | 1 | 65 | 12 (9)| 00:00:01 |
|* 1 | HASH JOIN | | 1 | 65 | 12 (9)| 00:00:01 |
| 2 | MERGE JOIN CARTESIAN| | 20 | 1040 | 8 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL | DEPT | 4 | 52 | 3 (0)| 00:00:01 |
| 4 | BUFFER SORT | | 5 | 195 | 5 (0)| 00:00:01 |
| 5 | TABLE ACCESS FULL | SALGRADE | 5 | 195 | 1 (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"."LOSAL" AND "A"."SAL"<="C"."HISAL")
在SQL语句中，dept与salgrade没有直接关联条件，HINT：ordered表示根据SQL语句中from后面的表的顺序依次关联，因为dept与salgrade没有直接关联条件，并且SQL语句中添加了HINT：ordered，并且SQL语句中两个表是依次放在from后面的，所以dept与salgrade只能进行笛卡尔连接
思考：当执行计划中有笛卡尔连接应该怎么优化呢？
首先应该检查表是否有关联条件，如果表没有关联条件，那么应该询问开发与业务人员为何表没有关联条件，是否为满足业务需求而故意不写关联条件。
其次应该检查离笛卡尔连接最近的表是否真的返回1行数据，如果返回行数真的就只有一行，那么走笛卡尔连接是没有问题的，如果返回行数超过1行，那就需要检查为什么Rows会估算错误，并且要纠正错误的Rows。纠正错误的Rows之后，优化器就不会走笛卡尔连接了。
可以使用HINT /*+ opt_param('_optimizer_mjc_enabled', 'false') */ 禁止笛卡尔连接。