Oracle-SQL优化-union和union all

用union all替代union
Union因为要进行去除重复值的处理，所以效率要低
适用场合：1-如果合并两个select结果集，没有刻意要去除重复行
          2-如果union的各个select结果集，不存在交集
Oracle的内部处理过程：
union操作：先执行union all操作获取所有数据合集，再执行去除重复行操作。所以如果没有重复的，不要用union，效率低

下面通过一个案例说明一下
Sql1：union联合两个结果集
select DEAL_SERIAL_NO,
       VERSION_NO,
       START_DATE,
       END_DATE,
       PERIOD,
       COMPD_METHOD
  from tbinstcfl
union
select INTER_CODE as DEAL_SERIAL_NO,
       VERSION_NO,
       START_DATE,
       END_DATE,
       PERIOD,
       COMPD_METHOD
  from tbbondcfl
执行计划如下图：一共耗时6ms

Union操作：要先执行UNION-ALL操作获取结果集，再执行SORT-UNIQUE操作，看后面的时间，这个操作耗费2ms，如果数据量非常多的话，这个操作耗时和资源是非常惊人的。
Sql2：union all联合两个结果集
select DEAL_SERIAL_NO,
       VERSION_NO,
       START_DATE,
       END_DATE,
       PERIOD,
       COMPD_METHOD
  from tbinstcfl
union all
select INTER_CODE as DEAL_SERIAL_NO,
       VERSION_NO,
       START_DATE,
       END_DATE,
       PERIOD,
       COMPD_METHOD
  from tbbondcfl
执行计划如下图：一共耗时3ms

Union all操作：少了去除重复操作。去重复一项和上面比，就少了2ms，再看两个执行计划的最后一步整合数据，sql1用了2ms，sql2用了1ms，效率是不是高了很多

union和union all关键字需要注意的问题是：
union 和 union all都可以将多个结果集合并，而不仅仅是两个，你可以将多个结果集串起来。
使用union和union all必须保证各个select 集合的结果有相同个数的列，并且每个列的类型是一样的。但列名则不一定需要相同，oracle会将第一个结果的列名作为结果集的列名。例如下面是一个例子：
select empno,ename from emp
union
select deptno,dname from dept
我们没有必要在每一个select结果集中使用order by子句来进行排序，我们可以在最后使用一条order by来对整个结果进行排序。例如：
select empno,ename from emp
union
select deptno,dname from dept
order by ename;

对多个结果集进行合并处理的关键字有union，union all，intersect，minus
Union：对两个结果集进行并集操作，不包括重复行，同时进行默认规则的排序；
Union All：对两个结果集进行并集操作，包括重复行，不进行排序；
Intersect：对两个结果集进行交集操作，不包括重复行，同时进行默认规则的排序；
Minus：对两个结果集进行差操作，不包括重复行，同时进行默认规则的排序。

对上面两个结果集进行intersect操作，
Sql3：
select DEAL_SERIAL_NO,
       VERSION_NO,
       START_DATE,
       END_DATE,
       PERIOD,
       COMPD_METHOD
  from tbinstcfl
intersect
select INTER_CODE as DEAL_SERIAL_NO,
       VERSION_NO,
       START_DATE,
       END_DATE,
       PERIOD,
       COMPD_METHOD
  from tbbondcfl
执行计划如下图：一共耗时7ms

对上面两个结果集进行intersect操作，
select DEAL_SERIAL_NO,
       VERSION_NO,
       START_DATE,
       END_DATE,
       PERIOD,
       COMPD_METHOD
  from tbinstcfl
minus
select INTER_CODE as DEAL_SERIAL_NO,
       VERSION_NO,
       START_DATE,
       END_DATE,
       PERIOD,
       COMPD_METHOD
  from tbbondcfl
执行计划如下图：一共耗时7ms

总结：对两个结果集的union，union all，intersect，minus操作
耗时：
Union all：3ms
1.两个结果集的全表扫描获取2.获取所有展现数据
Union：6ms
1.两个结果集的全表扫描获取2.去除重复操作3.获取所有展现数据
Intersect：7ms
1.两个结果集的全表扫描获取2.两个结果集的唯一性排序3.获取交集4.获取所有展现数据
Minus：7ms
1. 两个结果集的全表扫描获取2.两个结果集的唯一性排序3.获取差集4.获取所有展现数据
Intersect和minus的获取交集和差集如执行计划图，没有耗费时间，这个内部机制没有研究，不过由于之前是唯一性排序，所以即使耗时也很少，就当做不耗时了

了解内部运行机制：优化一切尽在掌握

评论

1 楼 yong7356 2015-03-05  

学习一下。。Oracle-SQL优化-union和union all