用Merge改写优化Update

最新推荐文章于 2023-11-17 11:30:33 发布
最新推荐文章于 2023-11-17 11:30:33 发布
阅读量741 收藏 1
点赞数
分类专栏： Oracle sql优化 Oracle SQL 优化 文章标签： Merge更新
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/wanbin6470398/article/details/84851817
版权

用Merge改写优化Update

1. 多列关联更新改写示例

1. 模拟数据
create table t_objects as select * from dba_objects;
create table t_tables as select * from dba_tables;
alter table t_objects add tablespace_name varchar2(30);
alter table t_objects add flag varchar2(2);

2. 现在需要把t_tables.object_id>10000的t_tables.tablespace_name与t_tables.'2'的值
更新到t_objects.tablespace_name 与t_objects.flag。

错误的写法：
update t_objects a
   set (a.tablespace_name, a.flag) =
       (select b.tablespace_name, '2'
          from t_tables b
         where a.owner = b.owner
           and a.object_name = b.table_name)
 where  a.object_id> 10000

因为如果where子句不加
(exists (select b.tablespace_name from t_tables b 
where a.owner = b.owner and a.object_name = b.table_name))
的话，会多处理一些没有关联上的数据，而这些没关联上的数据，是用null值更新的。

正确的写法：
update t_objects a
   set (a.tablespace_name, a.flag) =
       (select b.tablespace_name, '2'
          from t_tables b
         where a.owner = b.owner
           and a.object_name = b.table_name)
 where exists (select b.tablespace_name
          from t_tables b
         where a.owner = b.owner
           and a.object_name = b.table_name)
      and a.object_id> 10000

Elapsed: 00:00:00.67

Execution Plan
----------------------------------------------------------
Plan hash value: 2130220419

-----------------------------------------------------------------------------------
| Id  | Operation             | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------
|   0 | UPDATE STATEMENT      |           | 23341 |  3419K|  1027K  (5)| 03:25:26 |
|   1 |  UPDATE               | T_OBJECTS |       |       |            |          |
|*  2 |   HASH JOIN RIGHT SEMI|           | 23341 |  3419K|    93   (3)| 00:00:02 |
|   3 |    VIEW               | VW_SQ_1   |  3062 |   101K|    21   (0)| 00:00:01 |
|   4 |     TABLE ACCESS FULL | T_TABLES  |  3062 |   101K|    21   (0)| 00:00:01 |
|*  5 |    TABLE ACCESS FULL  | T_OBJECTS | 23346 |  2644K|    71   (2)| 00:00:01 |
|*  6 |   TABLE ACCESS FULL   | T_TABLES  |     1 |    51 |    21   (0)| 00:00:01 |
-----------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("A"."OWNER"="ITEM_1" AND "A"."OBJECT_NAME"="ITEM_2")
   5 - filter("A"."OBJECT_ID">10000)
   6 - filter("B"."OWNER"=:B1 AND "B"."TABLE_NAME"=:B2)

Note
-----
   - dynamic sampling used for this statement (level=2)


Statistics
----------------------------------------------------------
          0  recursive calls
       2614  db block gets
     255891  consistent gets
        507  physical reads
     643016  redo size
        839  bytes sent via SQL*Net to client
       1115  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
       2555  rows processed


3. 利用merge改写后

merge into t_objects a
using t_tables b
on (a.owner = b.owner and a.object_name = b.table_name)
when matched then
  update set a.tablespace_name = b.tablespace_name, a.flag = '2'
where a.object_id> 10000

Elapsed: 00:00:00.06

Execution Plan
----------------------------------------------------------
Plan hash value: 1970127410

------------------------------------------------------------------------------------------
| Id  | Operation            | Name      | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------
|   0 | MERGE STATEMENT      |           |  3062 |   768K|       |   567   (1)| 00:00:07 |
|   1 |  MERGE               | T_OBJECTS |       |       |       |            |          |
|   2 |   VIEW               |           |       |       |       |            |          |
|*  3 |    HASH JOIN         |           |  3062 |  2332K|  1656K|   567   (1)| 00:00:07 |
|   4 |     TABLE ACCESS FULL| T_TABLES  |  3062 |  1617K|       |    21   (0)| 00:00:01 |
|   5 |     TABLE ACCESS FULL| T_OBJECTS | 33077 |  7720K|       |    71   (2)| 00:00:01 |
------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - access("A"."OWNER"="B"."OWNER" AND "A"."OBJECT_NAME"="B"."TABLE_NAME")

Note
-----
   - dynamic sampling used for this statement (level=2)


Statistics
----------------------------------------------------------
          0  recursive calls
       2605  db block gets
        511  consistent gets
          0  physical reads
     607260  redo size
        840  bytes sent via SQL*Net to client
        955  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
       2555  rows processed
      
4. 优化后的物理读与逻辑读，都比之前减少很多，执行时间也少了许多。

2. 用Merge改写有聚合操作的Update

1. 模拟数据
create table t_objects as select * from dba_objects;
create table t_tables as select * from dba_tables;

insert into t_tables  select * from t_tables;
insert into t_tables  select * from t_tables;
insert into t_tables  select * from t_tables;
commit;

alter table t_objects add tablespace_name varchar2(30);
alter table t_objects add flag varchar2(2);


2. 更新数据到t_objects表中
update t_objects a
   set (a.tablespace_name, a.flag) =
       (select distinct b.tablespace_name, '2'
          from t_tables b
         where a.owner = b.owner
           and a.object_name = b.table_name)
 where exists (select distinct b.tablespace_name
          from t_tables b
         where a.owner = b.owner
           and a.object_name = b.table_name)
      and a.object_id> 10000

Elapsed: 00:00:04.84

Execution Plan
----------------------------------------------------------
Plan hash value: 1460256570

-----------------------------------------------------------------------------------
| Id  | Operation             | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------
|   0 | UPDATE STATEMENT      |           | 23346 |  3419K|  7190K  (2)| 23:58:10 |
|   1 |  UPDATE               | T_OBJECTS |       |       |            |          |
|*  2 |   HASH JOIN RIGHT SEMI|           | 23346 |  3419K|   225   (2)| 00:00:03 |
|   3 |    VIEW               | VW_SQ_1   | 26203 |   870K|   153   (1)| 00:00:02 |
|   4 |     TABLE ACCESS FULL | T_TABLES  | 26203 |   870K|   153   (1)| 00:00:02 |
|*  5 |    TABLE ACCESS FULL  | T_OBJECTS | 23346 |  2644K|    71   (2)| 00:00:01 |
|   6 |   HASH UNIQUE         |           |     3 |   153 |   154   (2)| 00:00:02 |
|*  7 |    TABLE ACCESS FULL  | T_TABLES  |     3 |   153 |   153   (1)| 00:00:02 |
-----------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("A"."OWNER"="ITEM_1" AND "A"."OBJECT_NAME"="ITEM_2")
   5 - filter("A"."OBJECT_ID">10000)
   7 - filter("B"."OWNER"=:B1 AND "B"."TABLE_NAME"=:B2)

Note
-----
   - dynamic sampling used for this statement (level=2)


Statistics
----------------------------------------------------------
         28  recursive calls
       5202  db block gets
    1972653  consistent gets
          0  physical reads
    1119588  redo size
        824  bytes sent via SQL*Net to client
       1144  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          3  sorts (memory)
          0  sorts (disk)
       2555  rows processed

3. merge改写
 merge into (select owner, object_name, tablespace_name, flag
               from t_objects
              where object_id > 10000) a
 using (select owner, table_name, tablespace_name
          from t_tables
         group by owner, table_name, tablespace_name) b
 on (a.owner = b.owner and a.object_name = b.table_name)
 when matched then
   update set a.tablespace_name = b.tablespace_name, a.flag = '2'

Elapsed: 00:00:00.07

Execution Plan
----------------------------------------------------------
Plan hash value: 1597301946

-------------------------------------------------------------------------------------------
| Id  | Operation             | Name      | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------
|   0 | MERGE STATEMENT       |           | 23346 |  1139K|       |   460   (2)| 00:00:06 |
|   1 |  MERGE                | T_OBJECTS |       |       |       |            |          |
|   2 |   VIEW                |           |       |       |       |            |          |
|   3 |    SORT GROUP BY      |           | 23346 |  4080K|       |   460   (2)| 00:00:06 |
|*  4 |     HASH JOIN         |           | 23346 |  4080K|  1616K|   458   (1)| 00:00:06 |
|   5 |      TABLE ACCESS FULL| T_TABLES  | 26203 |  1305K|       |   153   (1)| 00:00:02 |
|*  6 |      TABLE ACCESS FULL| T_OBJECTS | 23346 |  2918K|       |    71   (2)| 00:00:01 |
-------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   4 - access("OWNER"="OWNER" AND "OBJECT_NAME"="TABLE_NAME")
   6 - filter("OBJECT_ID">10000)

Note
-----
   - dynamic sampling used for this statement (level=2)


Statistics
----------------------------------------------------------
          9  recursive calls
       2623  db block gets
       1274  consistent gets
          0  physical reads
     643324  redo size
        840  bytes sent via SQL*Net to client
       1161  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          2  sorts (memory)
          0  sorts (disk)
       2555  rows processed  
       
4. merge 改写后，性能大大提升
DB-Engineer
关注
  • 0
    点赞
  • 踩
  • 1
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
生产环境Oracle中sql优化案例1（关于update与merge）
weixin_43519038的博客
10-17 518
生产环境Oracle中sql优化案例1一个关于update和merge into的案例案例简介生产原来的sql优化之后的sql为什么这么优化插入链接与图片如何插入一段漂亮的代码片生成一个适合你的列表创建一个表格设定内容居中、居左、居右SmartyPants创建一个自定义列表如何创建一个注脚注释也是必不可少的KaTeX数学公式新的甘特图功能，丰富你的文章UML 图表FLowchart流程图导出与导入导出导入 一个关于update和merge into的案例 你好！ 这是你第一次使用 Markdown编辑器 所
update修改为merge（max+decode）
小麦苗DBA宝典
08-03 716
update修改为merge（max+decode） ------------- 优化方法： 减少大表扫描次数采用max+decode方式 原sql语句： UPDATERKO_ACCT_STATUS ASETRMB_PAYMENT=(SELECTNVL(SUM(POSTING_AMT),0)FROMRKOT_ACCT_PMT_PRIOR BWHEREB.ACC...
利用Merge into 改写Update SQL 一例
dbtrd733355的博客
03-27 248
前言 客户说，生产系统最近CPU使用率经常达到100%，请DBA帮忙调查一下。 根据客户提供的情况描述及对应时间段，我导出AWR，发现如下问题： 11v41vaj06pjd ：每次执行消耗2,378,874.14 buffer 约等于18g 内存 bsfrz471nh9s4：每次执行消耗1,545,875.18 buffer 约等于12g 内...
[pandas] 详解pandas库的pd.merge函数
ande6905的博客
05-16 1034
本篇详细说明merge的应用，join 和concatenate的拼接方法的与之相似。 pd.merge(left, right, how='inner', on=None, left_on=None, right_on=None, left_index=False, right_index=False, sort=True, suffixe...
Python pd.merge()函数介绍（全）
热门推荐
m0_61635017的博客
04-15 1万+
在数据合并操作中，有两个操作函数和pd.merge()这两个函数在使用过程中经常会拿来比较，只要我们弄懂了其中重要参数的意义，理解每一个函数的用法，就能做到在那种环境适用那个函数,让我们通过本文深入理解pd.merge().参数表参数名作用left拼接的左侧DataFrame对象right拼接的右侧DataFrame对象on要加入的列或索引级别名称。必须在左侧和右侧DataFrame对象中找到。
update语句的优化-oracle .pdf
12-16
实际项目中遇到的问题总结：数据量百万级，千万级。Oracle中update语句的优化，一共四种方案，工作中遇到该类问题可以参考。
Merge Into 语句代替InsertUpdate
03-31
非常经典的SQL经验,适合于数据库初学者及长期从事软件开发者
Android开发之merge结合include优化布局
01-05
merge结合include优化android布局，效果不知道，个人感觉使用上也有很大的局限，不过还是了解一下，记录下来。 布局文件都要有根节点，但android中的布局嵌套过多会造成性能问题，于是在使用include嵌套的时候我们...
session中merge和update的区别
08-11
Hibernate中session的merge以及update方法
SQLServer中merge函数用法详解
01-21
它在SQL Server 2008被引入，它能将Insert,Update,Delete简单的并为一句。MSDN对于Merge的解释非常的短小精悍:”根据与源表联接的结果，对目标表执行插入、更新或删除操作。 MERGE 目标表 USING 源表 ON 匹配...
Merge Into 语句代替Insert/Update在Oracle中的应用实战
bruesz的专栏
11-20 3060
动机：想在Oracle中用一条SQL语句直接进行Insert/Update的操作。说明：在进行SQL语句编写时，我们经常会遇到大量的同时进行Insert/Update的语句 ，也就是说当存在记录时，就更新(Update)，不存在数据时，就插入(Insert)。实战：接下来我们有一个任务，有一个表T，有两个字段a,b，我们想在表T中做Insert/Update,如果存在，则更新T
oracle两种更新update和merge
taxue1011的博客
07-22 4460
update和merge只用于更新时，俩种方式是可互更换的 update适用于 1、更改单表速度快稳定性好； 2、某字段即是过滤条件又是更新字段，且该字段有选择性很强的索引时“update A set status=1 where id=1 andstatus=2 and idc in (表)” merge适用于 1、根据一张表或多表联合查询的连接条件对另外一张表进行查询，连接条件匹配上的进行UPDATE，无法匹配的执行INSERT...
update与merge基本语法规则、异同点、使用场景
最新发布
林世广
11-17 773
2、“UPDATE A SET i=(SELECT i FROM b WHERE a.id=b.id) WHERE NOT EXISTS (SELECT 1 FROM b WHERE a.id=b.id)”NOT exists部分需要额外消耗，可以用merge避免。3、修改一条已有的数据：使用update的时候不管出现什么情况都会去修改（即覆盖原有数据），而merge会去查询原有数据，如果一样的话，不会取修改，只有出现不同的内容的时候才会取修改。2、merge只需要遍历一次表，可以更新可以插入。
merge和update的区别
jinjiayu1993的博客
05-31 6304
今天已经底层内容的时候发现了一个神奇的东西merge（对于我这样的小白），从来没有见过去查了一下也是修改的功能，但是却与update有着明显的区别。 修改一条已有的数据：使用update的时候不管出现什么情况都会去修改（即覆盖原有数据）。而merge会去查询原有数据，如果一样的话，不会去修改，只有出现不同的内容的时候才会去修改。 merge还有个特点：当修改的数据id找不到的时候，update
连续使用merge和update梳理
随枫而弑
03-13 593
前几天，公司的老司机在群里发了一个问题供大家探讨：   刚遇到一个hibernate的问题，项目框架是struts2+ spring +hibernate3问题，在action层，分别调用了2个service 方法，比如叫aService.mergeEntity(entity)和bService.updateEntity(entity); 参数都有一个entity对象这个 entity ...
Sqlserver、oracle中Merge的使用方法,一个merge语句搞定多个Insert,Update,Delete操作
lusklusklusk的博客
10-13 4448
Sqlserver、oracle中Merge的使用方法,一个merge语句搞定多个Insert,Update,Delete操作
update 与 merge
weixin_30879169的博客
01-31 358
update 是平时使用比较多的更新语句 适用于数据量较小的情况下 merge 也是更新语句 但是更新速度很快 所以当数据量大的时候 建议使用这个方法 列： UPDATE表名1 gygxSETgygx.gyoid=(SELECTgy.pidFROM 表名2 gyWHEREgygx.oid=gy.oidANDgy.reltype=21) 将上面的...
mysql merge update_使用 MERGE 在单个语句中对表执行 UPDATE 和 DELETE 操作
weixin_30262217的博客
01-19 405
下面的示例使用 MERGE 根据 SalesOrderDetail 表中已处理的订单，每天更新 AdventureWorks 示例数据库中的 ProductInventory 表。 通过减去每天对 SalesOrderDetail 表中的每种产品所下的订单数，更新 ProductInventory 表的 Quantity 列。 如果某种产品的订下面的示例使用 MERGE 根据 SalesOrder...
merge与update区别
A_big_fish的博客
07-05 820
1. 数据库记录已存在，更改person的name为一个新的name。  merge方法打印出的日志如下:  hibernate: select person0_.id as id0_0_, person0_.name as name0_0_ from person person0_ where person0_.id=?  Hibernate: update person se
merge into 和update的区别
03-16
Merge 和 Update 是两种不同的 SQL 操作。 Merge 用于将两个表中的数据合并到一个表中。它检查两个表中是否存在相同的记录，如果存在则更新，如果不存在则插入。 Update 用于更新表中的数据。它更新表中满足特定...

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值