SQL性能优化:UNION ALL快于UNION

         
banq 16-07-29

如果两个join的数据集中重复元素能够移除,也就是你确信两个联合查询的数据集(内部WHERE语句)中没有重复元素,那么使用UNION ALL快于UNION,这样可以让数据库引擎优化内部查询( inner select)。这对 Oracle 11g及大部分数据库有效。

如果确认内部Where两个数据集没有重复元素,使用下面语句最快:

(SELECT * FROM TABLE_1 WHERE COL > 1) a
UNION ALL
(SELECT * FROM TABLE_2 WHERE COL > 1) b;


上句要快于:

(SELECT * FROM TABLE_1 WHERE COL > 1) a
UNION
(SELECT * FROM TABLE_2 WHERE COL > 1) b;


也快于:

SELECT * FROM (
(SELECT * FROM TABLE_1) a
UNION
(SELECT * FROM TABLE_2) b)
WHERE COL > 1;



Performance Tip for Tuning SQL with UNION - DZone