利用Merge代替复杂的UPDATE语句
来源：互联网 发布：mac外置光驱读不了光盘 编辑：程序博客网 时间：2024/05/17 21:54
有朋友问我一个update语句，下面两种写法哪个性能更高，一个是没HINT的，一个是有HINT的
UPDATE CS_PERFORMANCE_CURRENT C SET C.PERFORMANCE_SCORE = '0.00', C.ASSESS_DESC = '劳动纪律考核' WHERE C.TARGET_MODE_SEQ_ID = (SELECT D.SEQ_ID FROM CS_TARGET_MODEL_REL D, CS_AREA A WHERE D.ASSESS_ID = 1265 AND C.TARGET_MODE_SEQ_ID = D.SEQ_ID AND D.STAFF_ID = 8 AND C.COM_DATE = TO_DATE('20110801','YYYYMMDD') AND D.TARGET_MODEL_ID = 332 AND D.STATE = TO_CHAR(2) AND D.AREA_ID = A.AREA_ID AND A.AREA_NAME = '永兴支局');Plan hash value: 2321634139 ----------------------------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |----------------------------------------------------------------------------------------------------------| 0 | UPDATE STATEMENT | | 1 | 41 | 679 (1)| 00:00:09 || 1 | UPDATE | CS_PERFORMANCE_CURRENT | | | | ||* 2 | FILTER | | | | | || 3 | TABLE ACCESS FULL | CS_PERFORMANCE_CURRENT | 124K| 4968K| 664 (1)| 00:00:08 ||* 4 | FILTER | | | | | || 5 | NESTED LOOPS | | 1 | 52 | 3 (0)| 00:00:01 ||* 6 | TABLE ACCESS BY INDEX ROWID| CS_TARGET_MODEL_REL | 1 | 26 | 2 (0)| 00:00:01 ||* 7 | INDEX UNIQUE SCAN | PK_CS_TARGET_RULE_REL | 1 | | 1 (0)| 00:00:01 ||* 8 | TABLE ACCESS BY INDEX ROWID| CS_AREA | 1 | 26 | 1 (0)| 00:00:01 ||* 9 | INDEX UNIQUE SCAN | PK_CS_AREA_NEW | 1 | | 0 (0)| 00:00:01 |---------------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id):--------------------------------------------------- 2 - filter("C"."TARGET_MODE_SEQ_ID"= (SELECT "D"."SEQ_ID" FROM "CS_AREA" "A","CS_TARGET_MODEL_REL" "D" WHERE :B1=TO_DATE(' 2011-08-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "D"."SEQ_ID"=:B2 AND "D"."STAFF_ID"=8 AND "D"."ASSESS_ID"=1265 AND "D"."TARGET_MODEL_ID"=332 AND "D"."STATE"='2' AND "D"."AREA_ID"="A"."AREA_ID" AND "A"."AREA_NAME"='永兴支局')) 4 - filter(:B1=TO_DATE(' 2011-08-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss')) 6 - filter("D"."STAFF_ID"=8 AND "D"."ASSESS_ID"=1265 AND "D"."TARGET_MODEL_ID"=332 AND "D"."STATE"='2') 7 - access("D"."SEQ_ID"=:B1) 8 - filter("A"."AREA_NAME"='永兴支局') 9 - access("D"."AREA_ID"="A"."AREA_ID")已用时间: 00: 00: 00.29统计信息---------------------------------------------------------- 1 recursive calls 0 db block gets 32670 consistent gets 21 physical reads 0 redo size 536 bytes sent via SQL*Net to client 978 bytes received via SQL*Net from client 3 SQL*Net roundtrips to/from client 1 sorts (memory) 0 sorts (disk) 0 rows processedUPDATE /*+ PUSH_SUBQ(@D) */CS_PERFORMANCE_CURRENT C SET C.PERFORMANCE_SCORE = '0.00', C.ASSESS_DESC = '劳动纪律考核' WHERE C.TARGET_MODE_SEQ_ID = (SELECT /*+ QB_NAME(D) */ D.SEQ_ID FROM CS_TARGET_MODEL_REL D, CS_AREA A WHERE D.ASSESS_ID = 1265 AND C.TARGET_MODE_SEQ_ID = D.SEQ_ID AND D.STAFF_ID = 8 AND C.COM_DATE = TO_DATE('20110801','YYYYMMDD') AND D.TARGET_MODEL_ID = 332 AND D.STATE = TO_CHAR(2) AND D.AREA_ID = A.AREA_ID AND A.AREA_NAME = '永兴支局') Plan hash value: 4189652906 ----------------------------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |----------------------------------------------------------------------------------------------------------| 0 | UPDATE STATEMENT | | 1 | 39 | 43 (0)| 00:00:01 || 1 | UPDATE | CS_PERFORMANCE_CURRENT | | | | ||* 2 | TABLE ACCESS FULL | CS_PERFORMANCE_CURRENT | 1 | 39 | 40 (0)| 00:00:01 ||* 3 | FILTER | | | | | || 4 | NESTED LOOPS | | 1 | 47 | 3 (0)| 00:00:01 ||* 5 | TABLE ACCESS BY INDEX ROWID| CS_TARGET_MODEL_REL | 1 | 25 | 2 (0)| 00:00:01 ||* 6 | INDEX UNIQUE SCAN | PK_CS_TARGET_RULE_REL | 1 | | 1 (0)| 00:00:01 ||* 7 | TABLE ACCESS BY INDEX ROWID| CS_AREA | 1 | 22 | 1 (0)| 00:00:01 ||* 8 | INDEX UNIQUE SCAN | PK_CS_AREA_NEW | 1 | | 0 (0)| 00:00:01 |---------------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id):--------------------------------------------------- 2 - filter("C"."TARGET_MODE_SEQ_ID"= (SELECT /*+ PUSH_SUBQ QB_NAME ("D") */ "D"."SEQ_ID" FROM "CS_AREA" "A","CS_TARGET_MODEL_REL" "D" WHERE :B1=TO_DATE(' 2011-08-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "D"."SEQ_ID"=:B2 AND "D"."ASSESS_ID"=1265 AND "D"."STAFF_ID"=8 AND "D"."TARGET_MODEL_ID"=332 AND "D"."STATE"='2' AND "D"."AREA_ID"="A"."AREA_ID" AND "A"."AREA_NAME"='永兴支局')) 3 - filter(:B1=TO_DATE(' 2011-08-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss')) 5 - filter("D"."ASSESS_ID"=1265 AND "D"."STAFF_ID"=8 AND "D"."TARGET_MODEL_ID"=332 AND "D"."STATE"='2') 6 - access("D"."SEQ_ID"=:B1) 7 - filter("A"."AREA_NAME"='永兴支局') 8 - access("D"."AREA_ID"="A"."AREA_ID")统计信息---------------------------------------------------------- 1 recursive calls 0 db block gets 32670 consistent gets 21 physical reads 0 redo size 536 bytes sent via SQL*Net to client 978 bytes received via SQL*Net from client 3 SQL*Net roundtrips to/from client 1 sorts (memory) 0 sorts (disk) 0 rows processed
可以看到，逻辑读都是一样的，那么这个SQL，加不加HINT性能都一样。其实对于Oracle中的update 语句，如果SQL很复杂，需要关联多个表去做update，这个时候应该用
merge 语句代替 update，用Merge 改写之后 ：
MERGE INTO CS_PERFORMANCE_CURRENT AUSING (SELECT D.SEQ_ID FROM CS_TARGET_MODEL_REL D, CS_AREA M WHERE D.ASSESS_ID = 1265 AND D.STAFF_ID = 8 AND D.TARGET_MODEL_ID = 332 AND D.STATE = TO_CHAR(2) AND D.AREA_ID = M.AREA_ID AND M.AREA_NAME = '永兴支局') HON (A.TARGET_MODE_SEQ_ID = H.SEQ_ID)WHEN MATCHED THEN UPDATE SET A.PERFORMANCE_SCORE = '0.00', A.ASSESS_DESC = '劳动纪律考核' WHERE A.COM_DATE = TO_DATE('20110801', 'YYYYMMDD')； ---------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ---------------------------------------------------------------------------------------------------------- | 0 | MERGE STATEMENT | | 1 | 167 | 750 (1)| 00:00:10 | | 1 | MERGE | CS_PERFORMANCE_CURRENT | | | | | | 2 | VIEW | | | | | | |* 3 | HASH JOIN | | 1 | 210 | 750 (1)| 00:00:10 | | 4 | NESTED LOOPS | | 1 | 52 | 85 (2)| 00:00:02 | |* 5 | TABLE ACCESS FULL | CS_TARGET_MODEL_REL | 1 | 26 | 84 (2)| 00:00:02 | |* 6 | TABLE ACCESS BY INDEX ROWID| CS_AREA | 1 | 26 | 1 (0)| 00:00:01 | |* 7 | INDEX UNIQUE SCAN | PK_CS_AREA_NEW | 1 | | 0 (0)| 00:00:01 | | 8 | TABLE ACCESS FULL | CS_PERFORMANCE_CURRENT | 124K| 18M| 664 (1)| 00:00:08 | ---------------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 3 - access("A"."TARGET_MODE_SEQ_ID"="D"."SEQ_ID") 5 - filter("D"."STAFF_ID"=8 AND "D"."ASSESS_ID"=1265 AND "D"."TARGET_MODEL_ID"=332 AND "D"."STATE"='2') 6 - filter("M"."AREA_NAME"='永兴支局') 7 - access("D"."AREA_ID"="M"."AREA_ID") 统计信息---------------------------------------------------------- 333 recursive calls 0 db block gets 3474 consistent gets 3 physical reads 0 redo size 546 bytes sent via SQL*Net to client 998 bytes received via SQL*Net from client 3 SQL*Net roundtrips to/from client 15 sorts (memory) 0 sorts (disk) 0 rows processed
逻辑读下降了近10倍，这里 CS_PERFORMANCE_CURRENT 是全表扫描，它有100W的数据量，所以建立索引在TARGET_MODE_SEQ_ID列上
Plan hash value: 3908453434 -----------------------------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |-----------------------------------------------------------------------------------------------------------| 0 | MERGE STATEMENT | | 1 | 167 | 88 (2)| 00:00:02 || 1 | MERGE | CS_PERFORMANCE_CURRENT | | | | || 2 | VIEW | | | | | || 3 | TABLE ACCESS BY INDEX ROWID | CS_PERFORMANCE_CURRENT | 1 | 158 | 3 (0)| 00:00:01 || 4 | NESTED LOOPS | | 1 | 210 | 88 (2)| 00:00:02 || 5 | NESTED LOOPS | | 1 | 52 | 85 (2)| 00:00:02 ||* 6 | TABLE ACCESS FULL | CS_TARGET_MODEL_REL | 1 | 26 | 84 (2)| 00:00:02 ||* 7 | TABLE ACCESS BY INDEX ROWID| CS_AREA | 1 | 26 | 1 (0)| 00:00:01 ||* 8 | INDEX UNIQUE SCAN | PK_CS_AREA_NEW | 1 | | 0 (0)| 00:00:01 ||* 9 | INDEX RANGE SCAN | IDX_CS_PFC_CURRENT | 1 | | 1 (0)| 00:00:01 |----------------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id):--------------------------------------------------- 6 - filter("D"."STAFF_ID"=8 AND "D"."ASSESS_ID"=1265 AND "D"."TARGET_MODEL_ID"=332 AND "D"."STATE"='2') 7 - filter("M"."AREA_NAME"='永兴支局') 8 - access("D"."AREA_ID"="M"."AREA_ID") 9 - access("A"."TARGET_MODE_SEQ_ID"="D"."SEQ_ID")已用时间: 00: 00: 00.18统计信息---------------------------------------------------------- 0 recursive calls 0 db block gets 386 consistent gets 0 physical reads 0 redo size 544 bytes sent via SQL*Net to client 998 bytes received via SQL*Net from client 3 SQL*Net roundtrips to/from client 1 sorts (memory) 0 sorts (disk) 0 rows processed
现在逻辑读降低到386
在Oracle当中，强烈建议 =使用 merge 代替 update语句
- 利用Merge代替复杂的UPDATE语句
- 利用Merge代替复杂的UPDATE语句
- 用Merge代替复杂的Update
- Merge Into 语句代替Insert/Update
- 复杂的update语句
- Merge Into 语句代替Insert/Update在Oracle中的应用
- 一条比较复杂的update sql语句
- Java中利用设计模式来代替复杂的if...else...语句(三层以上的嵌套循环)
- Merge Into 语句代替Insert/Update在Oracle中的应用实战
- Merge Into 语句代替Insert/Update在Oracle中的应用实战
- Merge Into 语句代替Insert/Update在Oracle中的应用实战
- Merge Into 语句代替Insert/Update在Oracle中的应用实战
- Merge Into 语句代替Insert/Update在Oracle中的应用实战
- Merge Into 语句代替Insert/Update在Oracle中的应用实战
- Merge Into 语句代替Insert/Update在Oracle中的应用实战
- Merge Into 语句代替Insert/Update在Oracle中的应用实战
- Merge Into 语句代替Insert/Update在Oracle中的应用实战
- update select 语句和merge into语句
- Java中getResourceAsStream的用法
- 在hibernate第一个实例中遇到的问题和解决方法
- 高效使用搜索引擎
- 幸福是用来慢慢享用的
- JavaScript 静态页面传值 cookie
- 利用Merge代替复杂的UPDATE语句
- seo日常工作要旨
- linux 基础学习之二：ext2文件系统研究
- 转载Android开发中的多线程编程技术
- 变量延迟（下）
- 当python finally遇到break和sys.exit
- ant打包入门
- JavaScript replace() 方法
- 每日视界
