表连接方式(3)Sort Merge Join优化方案总结

一. 什么是Sort Merge Join(排序连接合并)?

表连接方式中,排序连接合并个人理解为多数处理非等值连接的表连接情况,例如<,> >=,<=,执行效率上看与HASH JOIN比有所降低(因有排序过程),但是如果两个表的连接列上均有索引,则有些场景下则可以避免排序过程(索引有序)。工作过程原理如下,A,B两表进行排序连接合并:

(1)根据谓词条件访问A表,对A表的结果集按照连接列进行排序,形成R1

(2)根据谓词条件访问B表,对B表的结果集按照连接列进行排序,形成R2

(3)Oracle官方认为Sort Merge Join是不存在驱动表与被启动表的概念的,但是最终的R1,R2进行合并工作时翻阅了诸多资料后,个人感觉是存在的,R1中取出第一条记录去R2中进行匹配,第二条记录..第三条记录..直到匹配完成R1所有记录。也就是驱动表对应结果集R1有多少条记录,R2则需要被匹配多少次,但是被驱动表只需要被扫描1次(PGA存入排序后的结果集就好,无需再次访问被驱动表)。该过程是在PGA中需进行匹配的,之前上文中提高,排序连接合并多数情况下执行效率是差与HASH JOIN的,因为HASH JOIN只需要把驱动表放入PGA中,但是排序连接合并需要将两表结果集均放入PGA中。

SMJ-2

smj-1

实验环境中,模拟了一个SMJ的表连接SQL,可以看到,T1为“驱动表”,T为被“驱动表”,之所以加上引号,是因为Oracle官方认为SMJ连接方式是不存在驱动表与被驱动表的概念的,可以看到,T1首先进行排序操作,T表进行排序操作,ID=4的执行计划中,T1结果集去T结果集中进行数据匹配过滤。

优化方案总结:

(1)两表关联如果是等值连接,则尽量不需要走Sort Merge Join,数据关联结果集大走HASH JOIN,数据返回量少走Nested Loop

(2)如过两表关联时非等值连接,可以在连接列上建立索引,避免昂贵Cost的排序的工作。如果结果集很大,则可以尝试先过滤将结果集变小,再进行Sort Merge Join。