查看: 7655|回复: 9
MERGE INTO 效率问题请教，谢谢斑竹们
论坛徽章:4
一个存储 过程中有， merge into 2000万的表A using 2000万的表B&&On 条件有索引A.A=B.B&&执行了3个小时&&才执行完
是不是merge into 处理 大数据量 效率本来就不高呢
还是&&数据库其他方面的问题
如果是 merge into 的问题， 请问有其他方案可以替代么？
论坛徽章:299
MERGE用好了，比UPDATE语句效率要高。
论坛徽章:33
ZALBB 发表于
MERGE用好了，比UPDATE语句效率要高。
嗯，的确是这样
论坛徽章:33
一般情况下，merge语句不会有问题
论坛徽章:112
如果机器可以的话，开并行，merge会快很多
论坛徽章:4
cdbdp 发表于
一般情况下，merge语句不会有问题
谢谢，我再多用些方法 实验下
论坛徽章:4
guoq.lee 发表于
如果机器可以的话，开并行，merge会快很多
谢谢，我加 HINT 试试
论坛徽章:4
ZALBB 发表于
MERGE用好了，比UPDATE语句效率要高。
是么，斑竹 能 简单 打个 比方 或举个小例子么，多谢！
论坛徽章:299
nb2sfc 发表于
是么，斑竹 能 简单 打个 比方 或举个小例子么，多谢！
论坛徽章:66
最快的方法是开并行后用CTAS方式建一个新表，旧表干掉或者rename，再把新表rename
itpub.net All Right Reserved. 北京皓辰网域网络信息技术有限公司版权所有　　　　
　北京市公安局海淀分局网监中心备案编号：　广播电视节目制作经营许可证：编号（京）字第1149号使用merge into语句程序性能，效率的提高-中国学网-中国IT综合门户网站-提供健康,养生,留学,移民,创业,汽车等信息
使用merge into语句程序性能，效率的提高
来源：互联网 更新时间： 20:25:04 责任编辑：王亮字体：
用户提出问题:使用merge into语句程序性能，效率的提高,具体如下:
oracle数据库我有一张临时表fax_no,lsh（主键）两个字段我有个功能 往这张临时表插入数据& 如果 这条数据fax_no存在什么都不做，如果不存在就insert部分代码如下：
//list2.get(i)-----&fax_no
//这次需要插入的总条数
//list2中有几万条数据
final int size = list2.size();
jdbcTemplate
.batchUpdate(
"merge into netfaxdb.fax_temp_faxno a"
+ " using (select ? as fax_no,? as lsh from dual)b"
+ " on (a.fax_no=b.fax_no)"
+ " when not matched then insert(fax_no,lsh) values(b.fax_no,b.lsh)",
new BatchPreparedStatementSetter() {
public void setValues(PreparedStatement ps,
int i) throws SQLException {
String lsh = UUIDGenerator.getUUID();
ps.setString(1, list2.get(i));
ps.setString(2, lsh);
public int getBatchSize() {
当fax_temp_faxno 这张表数据一多时& 程序就会变慢&& 有没有更好的办法
通过互联网整理获得以下解决方法:=================1楼=====================
fax_no 建立索引了吗
如果您还有更好的解决方法,请在最下面评论中留下您的解决方法
相关文章：
上一篇文章：下一篇文章：
最新添加资讯
24小时热门资讯
Copyright © 2004- All Rights Reserved. 中国学网 版权所有
京ICP备号-1 京公网安备02号7086人阅读
SQL TUNING（67）
有朋友问我一个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
_DATE = TO_DATE('','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:
----------------------------------------------------------------------------------------------------------
| Operation
| Bytes | Cost (%CPU)| Time
----------------------------------------------------------------------------------------------------------
0 | UPDATE STATEMENT
(1)| 00:00:09 |
| CS_PERFORMANCE_CURRENT |
TABLE ACCESS FULL
| CS_PERFORMANCE_CURRENT |
(1)| 00:00:08 |
NESTED LOOPS
(0)| 00:00:01 |
TABLE ACCESS BY INDEX ROWID| CS_TARGET_MODEL_REL
(0)| 00:00:01 |
INDEX UNIQUE SCAN
| PK_CS_TARGET_RULE_REL
(0)| 00:00:01 |
TABLE ACCESS BY INDEX ROWID| CS_AREA
(0)| 00:00:01 |
INDEX UNIQUE SCAN
| PK_CS_AREA_NEW
(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('
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('
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
----------------------------------------------------------
recursive calls
db block gets
consistent gets
physical reads
bytes sent via SQL*Net to client
bytes received via SQL*Net from client
SQL*Net roundtrips to/from client
sorts (memory)
sorts (disk)
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
_DATE = TO_DATE('','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:
----------------------------------------------------------------------------------------------------------
| Operation
| Bytes | Cost (%CPU)| Time
----------------------------------------------------------------------------------------------------------
0 | UPDATE STATEMENT
(0)| 00:00:01 |
| CS_PERFORMANCE_CURRENT |
TABLE ACCESS FULL
| CS_PERFORMANCE_CURRENT |
(0)| 00:00:01 |
NESTED LOOPS
(0)| 00:00:01 |
TABLE ACCESS BY INDEX ROWID| CS_TARGET_MODEL_REL
(0)| 00:00:01 |
INDEX UNIQUE SCAN
| PK_CS_TARGET_RULE_REL
(0)| 00:00:01 |
TABLE ACCESS BY INDEX ROWID| CS_AREA
(0)| 00:00:01 |
INDEX UNIQUE SCAN
| PK_CS_AREA_NEW
(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('
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('
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&)
----------------------------------------------------------
recursive calls
db block gets
consistent gets
physical reads
bytes sent via SQL*Net to client
bytes received via SQL*Net from client
SQL*Net roundtrips to/from client
sorts (memory)
sorts (disk)
rows processed
可以看到，逻辑读都是一样的，那么这个SQL，加不加HINT性能都一样。其实对于Oracle中的update 语句，如果SQL很复杂，需要关联多个表去做update，这个时候应该用
merge 语句代替 update，用Merge 改写之后 ：
MERGE INTO CS_PERFORMANCE_CURRENT A
USING (SELECT D.SEQ_ID
CS_TARGET_MODEL_REL D,
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 = '永兴支局') H
ON (A.TARGET_MODE_SEQ_ID = H.SEQ_ID)
WHEN MATCHED THEN
UPDATE SET A.PERFORMANCE_SCORE = '0.00', A.ASSESS_DESC = '劳动纪律考核' _DATE = TO_DATE('', 'YYYYMMDD')；
----------------------------------------------------------------------------------------------------------
| Operation
| Bytes | Cost (%CPU)| Time
----------------------------------------------------------------------------------------------------------
0 | MERGE STATEMENT
(1)| 00:00:10 |
| CS_PERFORMANCE_CURRENT |
(1)| 00:00:10 |
NESTED LOOPS
(2)| 00:00:02 |
TABLE ACCESS FULL
| CS_TARGET_MODEL_REL
(2)| 00:00:02 |
TABLE ACCESS BY INDEX ROWID| CS_AREA
(0)| 00:00:01 |
INDEX UNIQUE SCAN
| PK_CS_AREA_NEW
(0)| 00:00:01 |
TABLE ACCESS FULL
| CS_PERFORMANCE_CURRENT |
(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&)
----------------------------------------------------------
recursive calls
db block gets
consistent gets
physical reads
bytes sent via SQL*Net to client
bytes received via SQL*Net from client
SQL*Net roundtrips to/from client
sorts (memory)
sorts (disk)
rows processed
逻辑读下降了近10倍，这里 CS_PERFORMANCE_CURRENT 是全表扫描，它有100W的数据量，所以建立索引在TARGET_MODE_SEQ_ID列上
Plan hash value:
-----------------------------------------------------------------------------------------------------------
| Operation
| Bytes | Cost (%CPU)| Time
-----------------------------------------------------------------------------------------------------------
0 | MERGE STATEMENT
(2)| 00:00:02 |
| CS_PERFORMANCE_CURRENT |
TABLE ACCESS BY INDEX ROWID
| CS_PERFORMANCE_CURRENT |
(0)| 00:00:01 |
NESTED LOOPS
(2)| 00:00:02 |
NESTED LOOPS
(2)| 00:00:02 |
TABLE ACCESS FULL
| CS_TARGET_MODEL_REL
(2)| 00:00:02 |
TABLE ACCESS BY INDEX ROWID| CS_AREA
(0)| 00:00:01 |
INDEX UNIQUE SCAN
| PK_CS_AREA_NEW
(0)| 00:00:01 |
INDEX RANGE SCAN
| IDX_CS_PFC_CURRENT
(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
----------------------------------------------------------
recursive calls
db block gets
consistent gets
physical reads
bytes sent via SQL*Net to client
bytes received via SQL*Net from client
SQL*Net roundtrips to/from client
sorts (memory)
sorts (disk)
rows processed
现在逻辑读降低到386
在Oracle当中，强烈建议 =使用 merge 代替 update语句
参考知识库
* 以上用户言论只代表其个人观点，不代表CSDN网站的观点或立场
访问：1008396次
积分：12385
积分：12385
排名：第818名
原创：202篇
评论：578条
(1)(1)(1)(1)(1)(1)(1)(2)(1)(3)(1)(1)(3)(2)(1)(1)(1)(2)(2)(1)(1)(1)(1)(2)(1)(1)(1)(1)(4)(2)(2)(4)(1)(4)(4)(8)(1)(2)(2)(1)(2)(2)(1)(1)(3)(3)(4)(14)(7)(3)(38)(28)(25)