注册 登录  
 加关注
   显示下一条  |  关闭
温馨提示!由于新浪微博认证机制调整,您的新浪微博帐号绑定已过期,请重新绑定!立即重新绑定新浪微博》  |  关闭

PostgreSQL 中文网

 
 
 

日志

 
 

PostgreSQL:UNION 和 UNION ALL 操作的不同  

2013-08-16 16:18:57|  分类: Postgres基础 |  标签: |举报 |字号 订阅

  下载LOFTER 我的照片书  |

        今天读到老外的一篇关于 UNION 和 UNION ALL 操作对比的文章觉得很受用,原文
http://www.cybertec.at/common-mistakes-union-vs-union-all/ , 有必要实验下。

      UNION 操作一般用来合并多个结果集,尽管如此,仍然有细节需要注意,接着看以下
测试。

一 在 PostgreSQL9.3 中测试
--1.1 测试
 [pg93@redhatB ~]$ psql francs francs
psql (9.3beta1)
Type "help" for help.

francs=> select 1 union select 1;
 ?column? 
----------
        1
(1 row)
备注:这里预期是 2 条记录,结果却为 1 条。

--1.2 执行计划
 francs=> explain analyze  select 1 union select 1;
                                              QUERY PLAN                                              
------------------------------------------------------------------------------------------------------
 Unique  (cost=0.05..0.06 rows=2 width=0) (actual time=0.062..0.070 rows=1 loops=1)
   ->  Sort  (cost=0.05..0.06 rows=2 width=0) (actual time=0.058..0.061 rows=2 loops=1)
         Sort Key: (1)
         Sort Method: quicksort  Memory: 17kB
         ->  Append  (cost=0.00..0.04 rows=2 width=0) (actual time=0.006..0.017 rows=2 loops=1)
               ->  Result  (cost=0.00..0.01 rows=1 width=0) (actual time=0.003..0.005 rows=1 loops=1)
               ->  Result  (cost=0.00..0.01 rows=1 width=0) (actual time=0.002..0.003 rows=1 loops=1)
 Total runtime: 0.121 ms
(8 rows)
备注:根据以上,知道先进行排序,然后有个 Unique 操作去掉重复的行。

--1.3 pg 手册中的解释
[ { UNION | INTERSECT | EXCEPT } [ ALL | DISTINCT ] select ]
      Using the operators UNION, INTERSECT, and EXCEPT, the output of more than one SELECT statement can be combined to form a single result set. The UNION operator returns all rows that are in one or both of the result sets. The INTERSECT operator returns all rows that are strictly in both result sets. The EXCEPT operator returns the rows that are in the first result set but not in the second. In all three cases, duplicate rows are eliminated unless ALL is specified. The noise word DISTINCT can be added to explicitly specify eliminating duplicate rows. Notice that DISTINCT is the default behavior here, even though ALL is the default for SELECT itself. (See UNION Clause, INTERSECT Clause, and EXCEPT Clause below.)

UNION Clause
    The result of UNION does not contain any duplicate rows unless the ALL option is specified. ALL prevents elimination of duplicates. (Therefore, UNION ALL is usually significantly quicker than UNION; use ALL when you can.) DISTINCT can be written to explicitly specify the default behavior of eliminating duplicate rows.

备注: 上面解释得很清楚, UNION 默认使用 DISTINCT 模式,会删除重复的行。

--1.4 union distinct
 francs=> select 1 union distinct select 1;
 ?column? 
----------
        1
(1 row
 
--1.5 union all 
 francs=> select 1 union all select 1;
 ?column? 
----------
        1
        1
(2 rows)
 备注:"union distinct" 会删除重复的行, "union all" 是预期的结果,不会删除重复的行。
 

二 在 Oracle 10g 中测试
--2.1 测试
 15:38:03 SKYTF@skytf> select 1 from dual union select 1 from dual;

         1
----------
         1
 备注: union 操作和 PG 的测试结果一样。

--2.2 执行计划
 15:38:13 SKYTF@skytf> set autotrace on;
15:38:30 SKYTF@skytf> select 1 from dual union select 1 from dual;

         1
----------
         1

执行计划
----------------------------------------------------------
Plan hash value: 4080566713

-----------------------------------------------------------------
| Id  | Operation        | Name | Rows  | Cost (%CPU)| Time     |
-----------------------------------------------------------------
|   0 | SELECT STATEMENT |      |     2 |     6  (67)| 00:00:01 |
|   1 |  SORT UNIQUE     |      |     2 |     6  (67)| 00:00:01 |
|   2 |   UNION-ALL      |      |       |            |          |
|   3 |    FAST DUAL     |      |     1 |     2   (0)| 00:00:01 |
|   4 |    FAST DUAL     |      |     1 |     2   (0)| 00:00:01 |
-----------------------------------------------------------------


统计信息
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          0  consistent gets
          0  physical reads
          0  redo size
        401  bytes sent via SQL*Net to client
        385  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
          1  rows processed
备注:从 PLAN 中看出,oracle 的 union 和 pg 的 union 操作内部处理过程是类似的。

--2.3 union all
 15:44:32 SKYTF@skytf> select 1 from dual union all select 1 from dual;

         1
----------
         1
         1
备注: union all 操作和 PG 的测试结果一样。


--2.4 oracle 手册中的解释
UNION ALL Example 
     The UNION operator returns only distinct rows that appear in either result, while the UNION ALL operator returns all rows. The UNION ALL operator does not eliminate duplicate selected rows:


三 总结
  •    PG 和 ORACLE 对 UNION 的处理是一样的,即 union 操作会删除重复的行,而 union all 操作不会删除重复的行。
  •    由于 union 操作会对多个结果集进行排序然后删除重复的行,因此效率会比 union all 低很多。


四 参考

  评论这张
 
阅读(5321)| 评论(0)
推荐 转载

历史上的今天

在LOFTER的更多文章

评论

<#--最新日志,群博日志--> <#--推荐日志--> <#--引用记录--> <#--博主推荐--> <#--随机阅读--> <#--首页推荐--> <#--历史上的今天--> <#--被推荐日志--> <#--上一篇,下一篇--> <#-- 热度 --> <#-- 网易新闻广告 --> <#--右边模块结构--> <#--评论模块结构--> <#--引用模块结构--> <#--博主发起的投票-->
 
 
 
 
 
 
 
 
 
 
 
 
 
 

页脚

网易公司版权所有 ©1997-2016