SQL调优之六：排序合并连接（SortMergeJoins）
排序合并连接
排序合并连接是嵌套循环连接的变种。
如果两个数据集还没有排序，那么数据库会先对它们进⾏排序，这就是所谓的sort join操作。
对于数据集⾥的每⼀⾏，数据库会从上⼀次匹配到数据的位置开始探查第⼆个数据集，这⼀步就是Merge join操作。
优化器什么时候会考虑使⽤排序合并连接？
哈希连接会要求构建⼀个哈希表，然后探查它。⽽排序合并连接则要求两个排序了的数据集。
在以下情况，优化器在连接⼤数据集的时候会考虑使⽤排序合并连接来替代哈希连接：
连接条件是不等式，⽐如：<，<=，>或者>=，相对应的，哈希连接要求是等式条件。
因为其他的⼀些操作要求排序，优化器会认为使⽤排序合并连接的成本更低。同时，如果有索引的话，那么第⼀个数据集就能够避免使⽤排序。但是，第⼆个数据集不论有没有索引，都会要求排序
相对于嵌套循环连接来说，排序合并连接和哈希连接有着同样的优点：直接在PGA⾥⾯拿数据，不需要在SGA⾥⾯反复加闩锁，然后再读取缓存，减少了不必要的I/O。
⼀般情况下，哈希连接的性能是要⽐排序合并好的，因为排序的成本很⾼。
sql优化的几种方式但是，排序合并也有它的优点：
经过初始的排序后，合并过程是已经经过优化的了，在⽣成输出⾏的时候要更快
当哈希连接过程中的哈希表⽆法⼀次性完整构建在PGA⾥⾯的时候，排序合并的成本性能⽐要优于哈
希连接。
  　当内存⽆法存放整个哈希表，然后必须把⼀部分数据拷贝到磁盘的时候。数据库可能需要多次从磁盘上读取数据。⽽在排序合并连接的过程中，如果内存⽆法容纳两个数据集，那么数据库会把它们都写到磁盘上，但每个数据集不会读取超过⼀次。
排序合并连接是怎么⼯作的？
类似于嵌套循环连接，排序合并连接⼀样要连接连个数据集，只不过会对它们进⾏排序。
对于第⼀个数据集⾥的每⼀⾏，数据库会在第⼆个数据集到⼀个起始⾏，然后往下读，直到读不到匹配的⾏为⽌(这⾥会有些混淆，不要纠结，继续往下看)。
伪代码类似于：
READ data_set_1 SORT BY JOIN KEY TO temp_ds1
READ data_set_2 SORT BY JOIN KEY TO temp_ds2
READ ds1_row FROM temp_ds1
READ ds2_row FROM temp_ds2
WHILE NOT eof ON temp_ds1,temp_ds2
LOOP
  IF ( temp_ds1.key = temp_ds2.key ) OUTPUT JOIN ds1_row,ds2_row
  ELSIF ( temp_ds1.key <= temp_ds2.key ) READ ds1_row FROM temp_ds1
  ELSIF ( temp_ds1.key => temp_ds2.key ) READ ds2_row FROM temp_ds2
END LOOP
例⼦1
普通排序合并连接，两个数据集为 temp_ds1和temp_ds2
从上表来看，排序操作已经完成了，现在开始做匹配，数据库先读temp_ds1数据集的第⼀⾏10，然后去temp_ds2的表读第⼀⾏20，因为20⽐10⼤，⽽且数据是排序了的，证明temp_ds2后⾯的数据都⽐20⼤，所以已经没有必要再往下读数据了。
接下来，数据库开始读temp_ds1的下⼀⾏数据20，然后开始读temp_ds2的数据，第⼀⾏是20，匹配到了，继续读下⼀⾏，直到读到40这⼀⾏的时候，发现不匹配了，数据库就停下来了。
接着，数据库开始读temp_ds1的下⼀⾏数据30，这个时候它再去读temp_ds2，就不是从第⼀⾏开始读了，⽽是从上⼀次最后⼀个匹配的那⼀⾏开始，也就是最后⼀个20。然后再读到40的时候，发现不匹配，就⼜停下来了。
接着，读temp_ds1的下⼀⾏，40，读temp_ds2的最后匹配的那⼀⾏，20，然后继续往下读，直到读到60才停⽌。
排序合并连接就是通过这种⽅式，直到读到temp_ds2的最后⼀个70。相对于嵌套循环连接来说，它的优点是，不需要每⼀次匹配都从头读到尾。
例⼦2
使⽤索引的排序合并连接，
ployee_id, e.last_name, e.first_name, e.department_id,
d.department_name
FROM employees e, departments d
WHERE e.department_id = d.department_id
ORDER BY department_id;
连接的两个表，⼀个是DEPARTMENTS表，⼀个是EMPLOYEES表，DEPT_ID_PK是department表上的索引，从执⾏计划可以看出，数据库在读departments 表的时候使⽤了索引，避免了排序，单独对employees表做了排序，这⼀步会消耗很多CPU。
例⼦3
没有索引的排序合并连接，跟上个例⼦⼀样的语句，只不过这⼀次指定使⽤MERGE, 并且指定不使⽤索引。
SELECT /*+ USE_MERGE(d e) NO_INDEX(d) */ e.employee_id, e.last_name, e.first_name,
e.department_id, d.department_name
FROM employees e, departments d
WHERE e.department_id = d.department_id
ORDER BY department_id;
因为加了hint忽略索引，Step 2和Step 3的成本提⾼了67%
排序合并连接控制
使⽤USE_MERGE可以指定⼀条SQL⽤不⽤排序合并连接。
某些情况通过这个来覆盖优化器的选择是有好处的，⽐如优化器可以选择全表扫描来避免排序操作。
但是，如果是通过索引和单块读来全扫⼤表，⽽不是通过⼀个快速的全表扫描，则会导致成本的增加。

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。