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

太极虫的博客

好好学习,天天向上

 
 
 

日志

 
 

MERGE语句研究  

2008-04-02 15:51:24|  分类: ORACLE技术 |  标签: |举报 |字号 订阅

  下载LOFTER 我的照片书  |
昨天快下班的时候,看见Amber在写一个看上去很恐怖的存储过程,里面有两个FOR,和一大堆的IF,
我觉得很好奇就跑过去问她想实现一个什么样的需求,需要做这样恐怖的一个东东,
Amber告诉我说,
需要做两张表的比较,
假设为表A和表B,这两张表的字段相关,但不完全相同,
然后根据其中的几个字段判断A表中是否包含B表中的内容,
如果包含则根据B表更新A表剩于字段的值,
如果不包含则将B表中的字段插入到A表中去,

她打算写一个嵌套循环来解决这个问题,所以就有了我一开始看到的比较恐怖的存储过程,
为什么说比较恐怖,假设A表和B表都有10万条语句,想象一下这个循环要执行多少次?
出于效率的考虑,我建议她通过两个SQL语句解决问题,一条UPDATE加一条INSERT SELECT
然后扔给Amber一本讲解SQL语句的书
今天一早Amber告诉我说,她研究了一下,可以通过一条Merge语句解决问题,
考虑到Merge语句是9I以后才推出的,使用的也比较少,执行效率肯定不如常规的SQL语句高,
我也一直没做过这方面的测试,
一直想当然的认为,这种近似于PL/SQL程序的语句是通过牺牲执行效率换取了程序的简洁。
不过想要说服别人最为快捷的办法自然就是用测试数据说话了,于是我做了一个简单的测试,
首先创建两张表:
CREATE TABLE T AS SELECT ROWNUM ID, A.* FROM DBA_OBJECTS A;
CREATE TABLE T1 AS
 SELECT ROWNUM ID, OWNER, TABLE_NAME, CAST('TABLE' AS VARCHAR2(100)) OBJECT_TYPE
 FROM DBA_TABLES;

然后分别用MERGE语句和UPDATE,INSERT SELECT看看其实际执行的效率。
首先看看UPDATE,INSERT SELECT方式:
SQL> update t1 set
  2  id=(select id from t where t.owner = t1.owner AND T.OBJECT_NAME = T1.TABLE_NAME
  3  AND T.OBJECT_TYPE = T1.OBJECT_TYPE) where exists
  4      (select 'x' from t
  5      where t.owner=T1.OWNER AND T.OBJECT_NAME = T1.TABLE_NAME
  6      AND T.OBJECT_TYPE = T1.OBJECT_TYPE);
已更新3185行。

执行计划
----------------------------------------------------------                     
Plan hash value: 2240512800                                                    
                                                                               
----------------------------------------------------------------------------   
| Id  | Operation           | Name | Rows  | Bytes | Cost (%CPU)| Time     |   
----------------------------------------------------------------------------   
|   0 | UPDATE STATEMENT    |      |     1 |   193 |   198   (2)| 00:00:03 |   
|   1 |  UPDATE             | T1   |       |       |            |          |   
|*  2 |   HASH JOIN SEMI    |      |     1 |   193 |   198   (2)| 00:00:03 |   
|   3 |    TABLE ACCESS FULL| T1   |  3186 |   308K|     6   (0)| 00:00:01 |   
|   4 |    TABLE ACCESS FULL| T    | 66030 |  6061K|   191   (2)| 00:00:03 |   
|*  5 |   TABLE ACCESS FULL | T    |     1 |   107 |   191   (2)| 00:00:03 |   
----------------------------------------------------------------------------   
                                                                               
Predicate Information (identified by operation id):                            
---------------------------------------------------                            
                                                                               
   2 - access("T"."OWNER"="T1"."OWNER" AND                                     
              "T"."OBJECT_NAME"="T1"."TABLE_NAME" AND                          
              "T"."OBJECT_TYPE"="T1"."OBJECT_TYPE")                            
   5 - filter("T"."OWNER"=:B1 AND "T"."OBJECT_NAME"=:B2 AND                    
              "T"."OBJECT_TYPE"=:B3)                                           
                                                                               
Note                                                                           
-----                                                                          
   - dynamic sampling used for this statement                                  

统计信息
----------------------------------------------------------                     
        198  recursive calls                                                   
       3270  db block gets                                                     
    2676452  consistent gets                                                   
        852  physical reads                                                    
     784228  redo size                                                         
        678  bytes sent via SQL*Net to client                                  
        808  bytes received via SQL*Net from client                            
          4  SQL*Net roundtrips to/from client                                 
          4  sorts (memory)                                                    
          0  sorts (disk)                                                      
       3185  rows processed                                               
SQL> insert into t1 select T.ID, T.OWNER, T.OBJECT_NAME, T.OBJECT_TYPE from t
  2      where not exists
  3      (select 'x' from t1
  4      where t.owner=T1.OWNER AND T.OBJECT_NAME = T1.TABLE_NAME
  5      AND T.OBJECT_TYPE = T1.OBJECT_TYPE);
已创建54894行。

执行计划
----------------------------------------------------------                     
Plan hash value: 2840403628                                                    
                                                                               
--------------------------------------------------------------------------------
---                                                                            
                                                                               
| Id  | Operation          | Name | Rows  | Bytes |TempSpc| Cost (%CPU)| Time  |                                                                            
                                                                               
--------------------------------------------------------------------------------
                                                                           
                                                                               
|   0 | INSERT STATEMENT   |      | 66029 |    15M|       |  1127   (1)| 00:00:14 |                                                                            
                                                                               
|*  1 |  HASH JOIN ANTI    |      | 66029 |    15M|  7680K|  1127   (1)| 00:00:14 |                                                                            
                                                                               
|   2 |   TABLE ACCESS FULL| T    | 66030 |  6899K|       |   191   (2)| 00:00:03 |                                                                            
                                                                               
|   3 |   TABLE ACCESS FULL| T1   | 61947 |  8832K|       |    98   (2)| 00:00:02 |                                                                            
                                                                               
--------------------------------------------------------------------------------
                                                                           
                                                                               
                                                                               
Predicate Information (identified by operation id):                            
---------------------------------------------------                            
                                                                               
   1 - access("T"."OWNER"="T1"."OWNER" AND                                     
              "T"."OBJECT_NAME"="T1"."TABLE_NAME" AND                          
              "T"."OBJECT_TYPE"="T1"."OBJECT_TYPE")                            
                                                                               
Note                                                                           
-----                                                                          
   - dynamic sampling used for this statement                                  

统计信息
----------------------------------------------------------                     
        220  recursive calls                                                   
       2891  db block gets                                                     
       2204  consistent gets                                                   
          0  physical reads                                                    
    3084604  redo size                                                         
        677  bytes sent via SQL*Net to client                                  
        759  bytes received via SQL*Net from client                            
          4  SQL*Net roundtrips to/from client                                 
          8  sorts (memory)                                                    
          0  sorts (disk)                                                      
      54894  rows processed                                                    
SQL> spool out
下面来看看MERGE的执行效率
SQL> MERGE INTO T1 USING T
  2   ON (T.OWNER = T1.OWNER AND T.OBJECT_NAME = T1.TABLE_NAME AND T.OBJECT_TYPE = T1.OBJECT_TYPE)
  3   WHEN MATCHED THEN UPDATE SET T1.ID = T.ID
  4   WHEN NOT MATCHED THEN INSERT VALUES (T.ID, T.OWNER, T.OBJECT_NAME, T.OBJECT_TYPE);
58075 行已合并。

执行计划
----------------------------------------------------------                     
Plan hash value: 1828106337                                                    
                                                                               
--------------------------------------------------------------------------------
-----                                                                          
                                                                               
| Id  | Operation            | Name | Rows  | Bytes |TempSpc| Cost (%CPU)| Time
    |                                                                          
                                                                               
--------------------------------------------------------------------------------
-----                                                                          
                                                                               
|   0 | MERGE STATEMENT      |      | 55610 |    14M|       |  1366   (1)| 00:00:17 |                                                                          
                                                                               
|   1 |  MERGE               | T1   |       |       |       |            |          |                                                                          
                                                                               
|   2 |   VIEW               |      |       |       |       |            |          |                                                                          
                                                                               
|*  3 |    HASH JOIN OUTER   |      | 55610 |    19M|    10M|  1366   (1)| 00:00:17 |                                                                          
                                                                               
|   4 |     TABLE ACCESS FULL| T    | 55610 |    10M|       |   191   (2)| 00:00:03 |                                                                          
                                                                               
|   5 |     TABLE ACCESS FULL| T1   | 62742 |    10M|       |    98   (2)| 00:00:02 |                                                                          
                                                                               
--------------------------------------------------------------------------------
                                                                         
                                                                               
                                                                               
Predicate Information (identified by operation id):                            
---------------------------------------------------                            
                                                                               
   3 - access("T"."OBJECT_TYPE"="T1"."OBJECT_TYPE"(+) AND                      
              "T"."OBJECT_NAME"="T1"."TABLE_NAME"(+) AND "T"."OWNER"="T1"."OWNER"(+))                                                                          
                                                                               
                                                                               
Note                                                                           
-----                                                                          
   - dynamic sampling used for this statement                                  

统计信息
----------------------------------------------------------                     
        238  recursive calls                                                   
      61346  db block gets                                                     
       1974  consistent gets                                                   
          0  physical reads                                                    
   16487572  redo size                                                         
        675  bytes sent via SQL*Net to client                                  
        782  bytes received via SQL*Net from client                            
          4  SQL*Net roundtrips to/from client                                 
          8  sorts (memory)                                                    
          0  sorts (disk)                                                      
      58075  rows processed                                                    
SQL> spool out
 
测试结果让我大为惊讶,也使我改变了对新推出的一系列SQL语句的看法,
从测试结果看出,MERGE只需要对两张表各做一次全表扫描,扫描成本低,依据扫描的结果做HASH连接,
但是由于没有做FILTER,所以全表扫描获得的记录数较多,因此HASH运算的成本较高。
而update和insert select方式由于执行了了两条SQL语句,因此分别对两张表做了两次全表扫描,扫描成本较高,
但是由于存在FILTER所以做HASH连接的记录数少,所以HASH运算的成本低
 
综上所述,MERGE语句和UPDATE INSERT语句相比较,效率差距不大,在特定的情况下,MERGE语句的效率可能更高
这就给了我一个警示,必须随时准备接受新的事物,而且永远不要用老的眼光去看待新的事物,如果新的事物或者
技术能够给开发管理工作带来便捷,提高效率,那就需要立即接受它并加以及时的利用。
 
总结的不是很好,想请Peter.Wang协助总结一下。
 
 
 
  评论这张
 
阅读(23)| 评论(0)
推荐 转载

历史上的今天

评论

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

页脚

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