利用Merge代替复杂的UPDATE语句

最新推荐文章于 2024-09-19 21:27:19 发布
最新推荐文章于 2024-09-19 21:27:19 发布
阅读量245 收藏
点赞数
分类专栏： oracle sql 语句调优及相关技术 文章标签： merge performance date filter sql disk

有朋友问我一个update语句，下面两种写法哪个性能更高，一个是没HINT的，一个是有HINT的

[html]  view plain copy print ?
  1. UPDATE CS_PERFORMANCE_CURRENT C  
  2.    SET C.PERFORMANCE_SCORE = '0.00', C.ASSESS_DESC = '劳动纪律考核'  
  3.  WHERE C.TARGET_MODE_SEQ_ID =  
  4.        (SELECT D.SEQ_ID  
  5.           FROM CS_TARGET_MODEL_REL D, CS_AREA A  
  6.          WHERE D.ASSESS_ID = 1265  
  7.            AND C.TARGET_MODE_SEQ_ID = D.SEQ_ID  
  8.            AND D.STAFF_ID = 8  
  9.            AND C.COM_DATE = TO_DATE('20110801','YYYYMMDD')  
  10.            AND D.TARGET_MODEL_ID = 332  
  11.            AND D.STATE = TO_CHAR(2)  
  12.            AND D.AREA_ID = A.AREA_ID  
  13.            AND A.AREA_NAME = '永兴支局');  
  14.   
  15. Plan hash value: 2321634139  
  16.    
  17. ----------------------------------------------------------------------------------------------------------  
  18. | Id  | Operation                       | Name                   | Rows  | Bytes | Cost (%CPU)| Time     |  
  19. ----------------------------------------------------------------------------------------------------------  
  20. |   0 | UPDATE STATEMENT                |                        |     1 |    41 |   679   (1)| 00:00:09 |  
  21. |   1 |  UPDATE                         | CS_PERFORMANCE_CURRENT |       |       |            |          |  
  22. |*  2 |   FILTER                        |                        |       |       |            |          |  
  23. |   3 |    TABLE ACCESS FULL            | CS_PERFORMANCE_CURRENT |   124K|  4968K|   664   (1)| 00:00:08 |  
  24. |*  4 |    FILTER                       |                        |       |       |            |          |  
  25. |   5 |     NESTED LOOPS                |                        |     1 |    52 |     3   (0)| 00:00:01 |  
  26. |*  6 |      TABLE ACCESS BY INDEX ROWID| CS_TARGET_MODEL_REL    |     1 |    26 |     2   (0)| 00:00:01 |  
  27. |*  7 |       INDEX UNIQUE SCAN         | PK_CS_TARGET_RULE_REL  |     1 |       |     1   (0)| 00:00:01 |  
  28. |*  8 |      TABLE ACCESS BY INDEX ROWID| CS_AREA                |     1 |    26 |     1   (0)| 00:00:01 |  
  29. |*  9 |       INDEX UNIQUE SCAN         | PK_CS_AREA_NEW         |     1 |       |     0   (0)| 00:00:01 |  
  30. ----------------------------------------------------------------------------------------------------------  
  31.    
  32. Predicate Information (identified by operation id):  
  33. ---------------------------------------------------  
  34.    
  35.    2 - filter("C"."TARGET_MODE_SEQ_ID"= (SELECT "D"."SEQ_ID" FROM "CS_AREA"   
  36.               "A","CS_TARGET_MODEL_REL" "D" WHERE :B1=TO_DATE(' 2011-08-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss')   
  37.               AND "D"."SEQ_ID"=:B2 AND "D"."STAFF_ID"=8 AND "D"."ASSESS_ID"=1265 AND "D"."TARGET_MODEL_ID"=332   
  38.               AND "D"."STATE"='2' AND "D"."AREA_ID"="A"."AREA_ID" AND "A"."AREA_NAME"='永兴支局'))  
  39.    4 - filter(:B1=TO_DATE(' 2011-08-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))  
  40.    6 - filter("D"."STAFF_ID"=8 AND "D"."ASSESS_ID"=1265 AND "D"."TARGET_MODEL_ID"=332 AND   
  41.               "D"."STATE"='2')  
  42.    7 - access("D"."SEQ_ID"=:B1)  
  43.    8 - filter("A"."AREA_NAME"='永兴支局')  
  44.    9 - access("D"."AREA_ID"="A"."AREA_ID")  
  45.   
  46. 已用时间:  00: 00: 00.29  
  47.   
  48. 统计信息  
  49. ----------------------------------------------------------  
  50.           1  recursive calls  
  51.           0  db block gets  
  52.       32670  consistent gets  
  53.          21  physical reads  
  54.           0  redo size  
  55.         536  bytes sent via SQL*Net to client  
  56.         978  bytes received via SQL*Net from client  
  57.           3  SQL*Net roundtrips to/from client  
  58.           1  sorts (memory)  
  59.           0  sorts (disk)  
  60.           0  rows processed  
[html]  view plain copy print ?
  1. UPDATE /*+ PUSH_SUBQ(@D) */CS_PERFORMANCE_CURRENT C  
  2.    SET C.PERFORMANCE_SCORE = '0.00', C.ASSESS_DESC = '劳动纪律考核'  
  3.  WHERE C.TARGET_MODE_SEQ_ID =  
  4.        (SELECT /*+ QB_NAME(D) */ D.SEQ_ID  
  5.           FROM CS_TARGET_MODEL_REL D, CS_AREA A  
  6.          WHERE D.ASSESS_ID = 1265  
  7.            AND C.TARGET_MODE_SEQ_ID = D.SEQ_ID  
  8.            AND D.STAFF_ID = 8  
  9.            AND C.COM_DATE = TO_DATE('20110801','YYYYMMDD')  
  10.            AND D.TARGET_MODEL_ID = 332  
  11.            AND D.STATE = TO_CHAR(2)  
  12.            AND D.AREA_ID = A.AREA_ID  
  13.            AND A.AREA_NAME = '永兴支局')  
  14.              
  15. Plan hash value: 4189652906  
  16.    
  17. ----------------------------------------------------------------------------------------------------------  
  18. | Id  | Operation                       | Name                   | Rows  | Bytes | Cost (%CPU)| Time     |  
  19. ----------------------------------------------------------------------------------------------------------  
  20. |   0 | UPDATE STATEMENT                |                        |     1 |    39 |    43   (0)| 00:00:01 |  
  21. |   1 |  UPDATE                         | CS_PERFORMANCE_CURRENT |       |       |            |          |  
  22. |*  2 |   TABLE ACCESS FULL             | CS_PERFORMANCE_CURRENT |     1 |    39 |    40   (0)| 00:00:01 |  
  23. |*  3 |    FILTER                       |                        |       |       |            |          |  
  24. |   4 |     NESTED LOOPS                |                        |     1 |    47 |     3   (0)| 00:00:01 |  
  25. |*  5 |      TABLE ACCESS BY INDEX ROWID| CS_TARGET_MODEL_REL    |     1 |    25 |     2   (0)| 00:00:01 |  
  26. |*  6 |       INDEX UNIQUE SCAN         | PK_CS_TARGET_RULE_REL  |     1 |       |     1   (0)| 00:00:01 |  
  27. |*  7 |      TABLE ACCESS BY INDEX ROWID| CS_AREA                |     1 |    22 |     1   (0)| 00:00:01 |  
  28. |*  8 |       INDEX UNIQUE SCAN         | PK_CS_AREA_NEW         |     1 |       |     0   (0)| 00:00:01 |  
  29. ----------------------------------------------------------------------------------------------------------  
  30.    
  31. Predicate Information (identified by operation id):  
  32. ---------------------------------------------------  
  33.    
  34.    2 - filter("C"."TARGET_MODE_SEQ_ID"= (SELECT /*+ PUSH_SUBQ QB_NAME ("D") */ "D"."SEQ_ID" FROM   
  35.               "CS_AREA" "A","CS_TARGET_MODEL_REL" "D" WHERE :B1=TO_DATE(' 2011-08-01 00:00:00', 'syyyy-mm-dd   
  36.               hh24:mi:ss') AND "D"."SEQ_ID"=:B2 AND "D"."ASSESS_ID"=1265 AND "D"."STAFF_ID"=8 AND   
  37.               "D"."TARGET_MODEL_ID"=332 AND "D"."STATE"='2' AND "D"."AREA_ID"="A"."AREA_ID" AND   
  38.               "A"."AREA_NAME"='永兴支局'))  
  39.    3 - filter(:B1=TO_DATE(' 2011-08-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))  
  40.    5 - filter("D"."ASSESS_ID"=1265 AND "D"."STAFF_ID"=8 AND "D"."TARGET_MODEL_ID"=332 AND   
  41.               "D"."STATE"='2')  
  42.    6 - access("D"."SEQ_ID"=:B1)  
  43.    7 - filter("A"."AREA_NAME"='永兴支局')  
  44.    8 - access("D"."AREA_ID"="A"."AREA_ID")  
  45.   
  46. 统计信息  
  47. ----------------------------------------------------------  
  48.           1  recursive calls  
  49.           0  db block gets  
  50.       32670  consistent gets  
  51.          21  physical reads  
  52.           0  redo size  
  53.         536  bytes sent via SQL*Net to client  
  54.         978  bytes received via SQL*Net from client  
  55.           3  SQL*Net roundtrips to/from client  
  56.           1  sorts (memory)  
  57.           0  sorts (disk)  
  58.           0  rows processed  

 

可以看到，逻辑读都是一样的，那么这个SQL，加不加HINT性能都一样。其实对于Oracle中的update 语句，如果SQL很复杂，需要关联多个表去做update，这个时候应该用

merge 语句代替 update，用Merge 改写之后 ：

[html]  view plain copy print ?
  1. MERGE INTO CS_PERFORMANCE_CURRENT A  
  2. USING (SELECT D.SEQ_ID  
  3.        FROM   CS_TARGET_MODEL_REL D,  
  4.               CS_AREA             M  
  5.        WHERE  D.ASSESS_ID = 1265 AND  
  6.               D.STAFF_ID = 8 AND  
  7.               D.TARGET_MODEL_ID = 332 AND  
  8.               D.STATE = TO_CHAR(2) AND  
  9.               D.AREA_ID = M.AREA_ID AND  
  10.               M.AREA_NAME = '永兴支局') H  
  11. ON (A.TARGET_MODE_SEQ_ID = H.SEQ_ID)  
  12. WHEN MATCHED THEN  
  13.   UPDATE SET A.PERFORMANCE_SCORE = '0.00', A.ASSESS_DESC = '劳动纪律考核' WHERE A.COM_DATE = TO_DATE('20110801', 'YYYYMMDD')；  
  14.    
  15.  ----------------------------------------------------------------------------------------------------------  
  16.  | Id  | Operation                       | Name                   | Rows  | Bytes | Cost (%CPU)| Time     |  
  17.  ----------------------------------------------------------------------------------------------------------  
  18.  |   0 | MERGE STATEMENT                 |                        |     1 |   167 |   750   (1)| 00:00:10 |  
  19.  |   1 |  MERGE                          | CS_PERFORMANCE_CURRENT |       |       |            |          |  
  20.  |   2 |   VIEW                          |                        |       |       |            |          |  
  21.  |*  3 |    HASH JOIN                    |                        |     1 |   210 |   750   (1)| 00:00:10 |  
  22.  |   4 |     NESTED LOOPS                |                        |     1 |    52 |    85   (2)| 00:00:02 |  
  23.  |*  5 |      TABLE ACCESS FULL          | CS_TARGET_MODEL_REL    |     1 |    26 |    84   (2)| 00:00:02 |  
  24.  |*  6 |      TABLE ACCESS BY INDEX ROWID| CS_AREA                |     1 |    26 |     1   (0)| 00:00:01 |  
  25.  |*  7 |       INDEX UNIQUE SCAN         | PK_CS_AREA_NEW         |     1 |       |     0   (0)| 00:00:01 |  
  26.  |   8 |     TABLE ACCESS FULL           | CS_PERFORMANCE_CURRENT |   124K|    18M|   664   (1)| 00:00:08 |  
  27.  ----------------------------------------------------------------------------------------------------------  
  28.     
  29.  Predicate Information (identified by operation id):  
  30.  ---------------------------------------------------  
  31.     
  32.     3 - access("A"."TARGET_MODE_SEQ_ID"="D"."SEQ_ID")  
  33.     5 - filter("D"."STAFF_ID"=8 AND "D"."ASSESS_ID"=1265 AND "D"."TARGET_MODEL_ID"=332 AND   
  34.                "D"."STATE"='2')  
  35.     6 - filter("M"."AREA_NAME"='永兴支局')  
  36.     7 - access("D"."AREA_ID"="M"."AREA_ID")  
  37.             
  38.             
  39. 统计信息  
  40. ----------------------------------------------------------  
  41.         333  recursive calls  
  42.           0  db block gets  
  43.        3474  consistent gets  
  44.           3  physical reads  
  45.           0  redo size  
  46.         546  bytes sent via SQL*Net to client  
  47.         998  bytes received via SQL*Net from client  
  48.           3  SQL*Net roundtrips to/from client  
  49.          15  sorts (memory)  
  50.           0  sorts (disk)  
  51.           0  rows processed            


逻辑读下降了近10倍，这里 CS_PERFORMANCE_CURRENT 是全表扫描，它有100W的数据量，所以建立索引在TARGET_MODE_SEQ_ID列上

[html]  view plain copy print ?
  1. Plan hash value: 3908453434  
  2.    
  3. -----------------------------------------------------------------------------------------------------------  
  4. | Id  | Operation                        | Name                   | Rows  | Bytes | Cost (%CPU)| Time     |  
  5. -----------------------------------------------------------------------------------------------------------  
  6. |   0 | MERGE STATEMENT                  |                        |     1 |   167 |    88   (2)| 00:00:02 |  
  7. |   1 |  MERGE                           | CS_PERFORMANCE_CURRENT |       |       |            |          |  
  8. |   2 |   VIEW                           |                        |       |       |            |          |  
  9. |   3 |    TABLE ACCESS BY INDEX ROWID   | CS_PERFORMANCE_CURRENT |     1 |   158 |     3   (0)| 00:00:01 |  
  10. |   4 |     NESTED LOOPS                 |                        |     1 |   210 |    88   (2)| 00:00:02 |  
  11. |   5 |      NESTED LOOPS                |                        |     1 |    52 |    85   (2)| 00:00:02 |  
  12. |*  6 |       TABLE ACCESS FULL          | CS_TARGET_MODEL_REL    |     1 |    26 |    84   (2)| 00:00:02 |  
  13. |*  7 |       TABLE ACCESS BY INDEX ROWID| CS_AREA                |     1 |    26 |     1   (0)| 00:00:01 |  
  14. |*  8 |        INDEX UNIQUE SCAN         | PK_CS_AREA_NEW         |     1 |       |     0   (0)| 00:00:01 |  
  15. |*  9 |      INDEX RANGE SCAN            | IDX_CS_PFC_CURRENT     |     1 |       |     1   (0)| 00:00:01 |  
  16. -----------------------------------------------------------------------------------------------------------  
  17.    
  18. Predicate Information (identified by operation id):  
  19. ---------------------------------------------------  
  20.    
  21.    6 - filter("D"."STAFF_ID"=8 AND "D"."ASSESS_ID"=1265 AND "D"."TARGET_MODEL_ID"=332 AND   
  22.               "D"."STATE"='2')  
  23.    7 - filter("M"."AREA_NAME"='永兴支局')  
  24.    8 - access("D"."AREA_ID"="M"."AREA_ID")  
  25.    9 - access("A"."TARGET_MODE_SEQ_ID"="D"."SEQ_ID")  
  26.   
  27. 已用时间:  00: 00: 00.18  
  28.   
  29. 统计信息  
  30. ----------------------------------------------------------  
  31.           0  recursive calls  
  32.           0  db block gets  
  33.         386  consistent gets  
  34.           0  physical reads  
  35.           0  redo size  
  36.         544  bytes sent via SQL*Net to client  
  37.         998  bytes received via SQL*Net from client  
  38.           3  SQL*Net roundtrips to/from client  
  39.           1  sorts (memory)  
  40.           0  sorts (disk)  
  41.           0  rows processed  


现在逻辑读降低到386

在Oracle当中，强烈建议 =使用 merge 代替 update语句


转载：http://blog.csdn.net/robinson1988/article/details/7042543#comments

yrg5101
关注
专栏目录
mysql中update子查询,update 子查询使用介绍
weixin_31281613的博客
03-17 4431
基础知识1， 关联子查询和非关联子查询在非关联子查询中，内部查询只执行一次并返回它的值给外部查询，然后外部查询在它的处理中使用内部查询返回给它的值。而在关联子查询中，对于外部查询返回的每一行数据，内部查询都要执行一次。另外，在关联子查询中是信息流是双向的。外部查询的每行数据传递一个值给子查询，然后子查询为每一行数据执行一次并返回它的记录。然后，外部查询根据返回的记录做出决策。如：SELECT o1...
oracle数据匹配merge into的实例详解
12-16
Oracle的`MERGE INTO`语句是数据库管理中用于数据集成和同步的一种高效工具，尤其在处理大量数据的场景下，它可以替代多个独立的INSERT、UPDATE和DELETE操作。在这个实例中，我们看到如何利用`MERGE INTO`来解决特定...
oracle insert和update,INSERTUPDATE和MERGE (转)
weixin_35886095的博客
04-08 304
/*用COURSE表为模板，创建一张新表COURSE_NEW，并且包括COURSE表的所有记录 */CREATE TABLE COURSE_NEW AS SELECT * FROM COURSE;/*清空COURSE_NEW表中的所有记录*/TRUNCATETABLE COURSE_NEW;/*往COURSE_NEW表中增加以下记录：NO COURSE_NAMEA001 ORACLE数据库管理A0...
update merge 大表性能优化 index的作用巨大
jnrjian的博客
04-27 1089
说明：查表a的所有数据，循环每条数据，验证该条数据是否符合exists(select 1 from 表b where a.字段2=b.字段2)条件，如果是则执行(select b.字段1 from 表b where a.字段2=b.字段2)查询，查到对应的值更新a.字段1中。--如果A表巨大，B表也大，而且不能再B上建index 如何处理呢，可以先用with as将AB表join后的结果集取出来，再进行update，with as不能建index，所以，可以用临时表解决。近日，在项目中遇到一个需求。
Hibernate中update()和merge()的区别
guizishou00的博客
08-24 815
今天练习项目时遇到到这个问题，查了下资料特此汇总下下面的这些参资料，简单明了，感谢！1、初学hibernate时，对于session中的update()和merge()两个方法，很容易搞混，觉得两种都能用，有时候，又觉得不知道该用哪一种。 实际上，这两个方法，是有很大区别的 首先，对于一个处于detatch状态的实体对象来说，要将其中的修改，合并到数据库中，有两种方法，一种方式，是调用updat
update子查询
SKY的专栏
03-27 5695
基础知识 1， 关联子查询和非关联子查询 在非关联子查询中，内部查询只执行一次并返回它的值给外部查询，然后外部查询在它的处理中使用内部查询返回给它的值。而在关联子查询中，对于外部查询返回的每一行数据，内部查询都要执行一次。另外，在关联子查询中是信息流是双向的。外部查询的每行数据传递一个值给子查询，然后子查询为每一行数据执行一次并返回它的记录。然后，外部查询根据返回的记录做出决策。 如： SELEC...
认识oracle的update更新
wcqlwyt的博客
06-18 397
这两天给新同事安排了一个工作，即做一个update 的级联更新，在实际操作中发现了一个问题。就是对于oracle的更新的语法，大部分人尤其是学过SqlServer的人在使用oracle的时候
金仓数据库 KingbaseES SQL 语言参考手册 (20. SQL语句: MERGE 到 VALUES）
arthemis_14的博客
07-26 935
本章包含以下SQL语句:MERGEMOVENOTIFYPREPAREPREPARE TRANSACTIONPURGE TABLEREASSIGN OWNEDREFRESH MATERIALIZED VIEWREINDEXRELEASE SAVEPOINTRESETREVOKEROLLBACKROLLBACK PREPAREDROLLBACK TO SAVEPOINTSAVEPOINTSECURITY LABELSELECTSELECT INTOSETSET CONSTRAINTSSET ROLESET SE
oracle的sql语句高级查询,sql高级查询语句
weixin_39939668的博客
04-06 379
1.集合操作学习oracle中集合操作的有关语句，掌握union,union all,minus,interest的使用,能够描述结合运算，并且能够将多个查询组合到一个查询中去，能够控制行返回的顺序。包含集合运算的查询称为复合查询。见表格1-1表1-1Operator Returns contentUNION 由每个查询选择的所有不重复的行 ...
MySQL优化系列17-MySQL SQL语句优化
只是甲的博客
07-21 4700
备注:测试数据库版本为MySQL 8.0 文章目录一.优化select语句1.1 where子句优化1.2 IS NULL 优化1.3 ORDER BY优化1.4 group by 优化1.4.1 宽松的索引扫描1.4.2 紧索引扫描1.5 distinct优化1.6 Limit优化1.7 避免全表扫描二.优化DML语句2.1 优化insert语句2.2 优化update语句2.3 优化delete语句参考: 一.优化select语句 1.1 where子句优化 剔除重复的条件 ((a AND b
在sqlserver中如何使用CTE解决复杂查询问题
09-10
CTE的定义包括一个或多个SELECT语句，可以用于单条INSERT、UPDATE、MERGE或DELETE语句，也可以在CREATE VIEW语句中作为SELECT语句的一部分。特别地，CTE允许递归查询，即CTE可以引用自身，这对于处理层次结构数据...
LeetCode_sql_day28（1767.寻找没有被执行的任务对）
最新发布
Darling博客
09-19 481
掌握 用recursive循环 构造数列先用1作为起点再将一个值作为where 终点 此处来源表为recursive后的表。
SQL Server全方位指南：从入门到高级详解
qq_43535970的博客
09-19 909
本文为您提供了关于SQL Server的全面教程，覆盖了入门、进阶和高级三大部分。首先，我们介绍了SQL Server的基础知识，包括数据库创建、表操作、数据查询、更新等基本SQL语法。接着，深入探讨了事务处理、索引优化、联合查询、视图、存储过程及触发器等进阶技术。最后，文章详细讲解了查询优化、分区表、高可用性、灾难恢复、透明数据加密以及大数据支持等高级特性。无论您是初学者还是高级用户，都能通过本文获得深入的SQL Server知识与实践技巧。
SqlDb_.cs 与 csharp_SqlDb.h
weixin_42944928的博客
09-16 423
【代码】SqlDb_.cs 与 csharp_SqlDb.h。
经典sql题（一）求连续登录不少于三天用户
m0_58076578的博客
09-14 1016
去重提取日期查询：提取每个用户的唯一登录日期。结果表table1：显示每个用户的唯一日期。为每个用户生成序列号查询：为每个用户的日期生成序号，并计算date2。结果表table2：显示用户的日期和对应的date2。步骤使用函数，按照日期顺序为每个用户的登录日期分配一个序号。例如，对于用户 ID = 1，假设他们的日期是2023-10-012023-10-02和2023-10-03将为它们生成序号 1、2 和 3。date2是通过将每个日期减去它的序号得到的。这个操作的目的是为了检测连续的日期。
sqlgun靶场通关攻略
2301_80402555的博客
09-13 727
输入 -1' union select 1,2,3# ，页面出现回显点。
Postgresql导入sql文件数据
fxtxz2的专栏
09-13 351
经常有工友喊我导入一下sql到postgresql数据库中。今天就用命令行来导入sql数据。
Oracle Merge 语句：高效整合Insert和Update操作
Oracle SQL中的`Merge Into`语句是一种高效且灵活的数据操作方式，它在处理数据时可以同时执行插入（Insert）和更新（Update）操作，从而避免了传统`Insert`和`Update`操作可能导致的重复记录或数据不一致问题。...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值