加载中…[ORACLE][GREENPLUM]关于full outer join替代写法的总结
(2010-11-18 16:14:47)
标签：
oraclegreemplumfullouterjoin外连接 |
分类： Greenplum |
| 本周ETL测试方法讨论时Bill提到了一种full outer join的替代写法，由于full outer
join是一种比较低效的操作，所以如果有高效的替代写法，自然非常好，于是我对full outer
join的一些替代写法做了一下总价，由于实验的篇幅过多，我这里只给出结论，会显得更清晰。 先说Oracle数据库： 1.常规写法： select * from t01 full join t02 on t01.id=t02.id； 这是常规写法，没有什么好说的。 2.网上看到的一种替代写法： select * from t01,t02 where t01.id=t02.id(+) union select * from t01,t02 where t02.id=t01.id(+)； 就是用一个左外连接union一个又外连接。我是坚决反对这种写法的。 首先，这种写法与full outer join并不完全等价：如果t01中有重复记录或者t02中有重复记录，full outer join并不会去重，但写法2会去重。 其次，这种写法的效率很低，其实 full join 在内部执行时并不需要排序，但是写法2使用了union，要做排序去重操作，这个操作是效率很低的。 3.我之前总结的一种等价写法： select * from t01,t02 where t01.id=t02.id(+) union all select null,null,t02.id,t02.city from t02 where not exists (select 1 from t01 where t01.id=t02.id)； 其实我总结这种等价写法主要是让大家清楚Oracle的full outer join在底层是如何实现的。对，就是这么实现的，写法3的执行计划和写法1的执行计划一摸一样。 这种写法唯一的好处就是两个外关联可以分别加不同的提示，有的时候执行效率会比full outer join 高一些。 4.Bill说的替代写法： select id, max(name), max(city) from ( select id, name, null as city from t01 union all select id, null as name,city from t02 )； 我仔细研究了一下这种写法。得出的结论是这种写法在Oracle下性能并不明显优于写法1。 首先，这种写法与full join并不完全等价： a.这种写法要求id不能有空值 b.与写法2一样，它会做去重操作 c.如果我想通过full outer join 取t01和t02非交集的部分，即条件中加上where t01.id is null or t02.id is null，那么这种替代写法就无能为力了。 然后我做了很多性能测试，得出的结论是： （1）如果两个表的数据量很小时（执行操作全部在内存中完成），那么两种写法的效率几乎是一样的。 （2）如果其中一个表比较小，另一个表很大时，那么full outer join的性能要明显优于这种替代写法。 （3）如果两个表都非常大，那么full outer join的性能要略差于这种替代写法。 究其原因主要与HASH算法有关，这里就不详细说明了。 不过Bill提到的这种替代算法是用在GP上，就要另当别论了。因为Oracle也知道自己的full outer join性能比较差，所以它一直在优化着full outer join的实现，平心而论，目前Oracle的full outer join性能已经很好了，远优越于其他数据库。因此在GP上用这种替代算法肯定比full outer join的性能要好。同时还有一个原因，GP对full outer join有很严格的限制，为了提高效率，GP要求全外连接的连接条件必须是表的分布键，否则就会报如下错误： ERROR: 所以这种替代写法在GP上还是很有用处的。不过不要忘记这种替代写法不是等价写法，有上面我提到的3种限制哦。 |
喜欢
0
赠金笔