-
Notifications
You must be signed in to change notification settings - Fork 0
/
cbo_smj
104 lines (66 loc) · 6.65 KB
/
cbo_smj
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
排序合并连接(SORT MERGE JOIN)
哈希连接主要用于处理两表等值关联返回大量数据。
排序合并连接主要用于处理两表非等值关联，比如>，>=，<，<=，<>，但是不能用于instr，substr，like，regexp_like关联，instr，substr，like，regexp_like关联只能走嵌套循环。
现有如下SQL：
select * from a,b where a.id>=b.id;
A表有十万条数据，B表二十万条数据，A表与B表的ID列都是从1开始每次加1。
该SQL是非等值连接，因此不能进行HASH JOIN。
假如该SQL走的是嵌套循环，A作为驱动表，B作为被驱动表，那么B表会被扫描十万次。前文提到，嵌套循环被驱动表连接列要包含在索引中，那么B表的ID列需要创建一个索引，嵌套循环会进行传值，当A表通过ID列传值超过10000的时候，B表通过ID列的索引返回数据每次都会超过10000条，这个时候会造成B表大量回表。所以该SQL不能走嵌套循环，只能走排序合并连接。
排序合并连接的算法：两表关联，先对两个表根据连接列进行排序，将较小的表作为驱动表(Oracle官方认为排序合并连接没有驱动表，笔者认为是有的)，然后从驱动表中取出连接列的值，到已经排好序的被驱动表中匹配数据，如果匹配上数据，就关联成功。驱动表返回多少行，被驱动表就要被匹配多少次，这个匹配的过程类似嵌套循环，但是嵌套循环是从被驱动表的索引中匹配数据，而排序合并连接是在内存中(PGA中的work area)匹配数据。
非等值 关联 相当于 小型 笛卡尔积
在测试账号scott中，运行如下SQL：
SQL> select /*+ gather_plan_statistics */ e.ename, e.job,
2 d.dname from emp e, dept d where e.deptno >= d.deptno;
......省略输出结果......
获取执行计划：
SQL> select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------
SQL_ID f673my5x7tkkg, child number 0
-------------------------------------
select /*+ gather_plan_statistics */ e.ename, e.job, d.dname from
emp e, dept d where e.deptno >= d.deptno
Plan hash value: 844388907
-------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
-------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 31 |00:00:00.01 | 15 |
| 1 | MERGE JOIN | | 1 | 3 | 31 |00:00:00.01 | 15 |
| 2 | TABLE ACCESS BY INDEX ROWID| DEPT | 1 | 4 | 4 |00:00:00.01 | 8 |
| 3 | INDEX FULL SCAN | PK_DEPT | 1 | 4 | 4 |00:00:00.01 | 4 |
|* 4 | SORT JOIN | | 4 | 14 | 31 |00:00:00.01 | 7 |
| 5 | TABLE ACCESS FULL | EMP | 1 | 14 | 14 |00:00:00.01 | 7 |
-------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("E"."DEPTNO">="D"."DEPTNO")
filter("E"."DEPTNO">="D"."DEPTNO")
执行计划中离MERGE JOIN关键字最近的表就是驱动表。这里DEPT就是驱动表，EMP就是被驱动表。驱动表DEPT只扫描了一次(Id=2，Starts=1)，被驱动表EMP也只扫描了一次(Id=5,Starts=1)。
因为DEPT走的是INDEX FULL SCAN，INDEX FULL SCAN返回的数据是有序的，所以DEPT表就不需要排序了，EMP走的是全表扫描，返回的数据是无序的，所以EMP表在PGA中进行了排序。在实际工作中，一定要注意INDEX FULL SCAN返回了多少行数据，如果INDEX FULL SCAN返回的行数太多，应该强制走全表扫描，
现在强制DEPT表走全表扫描，查看执行计划：
SQL> select /*+ full(d) */
2 e.ename, e.job, d.dname
3 from emp e, dept d
4 where e.deptno >= d.deptno;
31 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1407029907
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3 | 90 | 8 (25)| 00:00:01 |
| 1 | MERGE JOIN | | 3 | 90 | 8 (25)| 00:00:01 |
| 2 | SORT JOIN | | 4 | 52 | 4 (25)| 00:00:01 |
| 3 | TABLE ACCESS FULL| DEPT | 4 | 52 | 3 (0)| 00:00:01 |
|* 4 | SORT JOIN | | 14 | 238 | 4 (25)| 00:00:01 |
| 5 | TABLE ACCESS FULL| EMP | 14 | 238 | 3 (0)| 00:00:01 |
----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("E"."DEPTNO">="D"."DEPTNO")
filter("E"."DEPTNO">="D"."DEPTNO")
从执行计划中看到，DEPT走的是全表扫描，因为全表扫描返回的数据是无序的，因此DEPT在PGA中进行了排序。
如果两表是等值关联，一般不建议走排序合并连接，因为排序合并连接需要将两个表放入PGA中，而HASH连接只需要将驱动表放入PGA中，排序合并连接相比HASH连接，需要耗费更多的PGA。即使排序合并连接中有一个表走的是INDEX FULL SCAN，但是另外一个表也需要放入PGA中，而这个表往往是大表，如果走HASH连接，大表会作为被驱动表，是不会被放入PGA中的，因此，两表等值关联，要么走NL(返回数据量少)，要么走HASH(返回数据量多)，一般情况下不要走SMJ。
思考：怎么优化排序合并连接？
如果两表关联是等值关联，走的是排序合并连接，可以将表连接方式改为HASH连接。如果两表关联是非等值关联，比如>，>=，<，<=，<>，这时应该先从业务上入手，尝试将非等值关联改写为等值关联，因为非等值关联返回的结果集“类似”于笛卡尔笛卡儿积了，当两个表都比较大的时候，非等值关联返回的数据量相当“恐怖”。如果没有办法将非等值关联改写为等值关联，可以考虑增加两表的限制条件，将两个表数据量缩小，最后可以考虑开启并行查询加快SQL执行速度。