Merge Into 快速更新大量数据

一、场景描述

接到几个数据修改的单子,需求是根据要求将给出的数据字段更新到表里边,数据量级第一次10万,第二次大约在20万左右,第三次300万。之前没有更新大量数据的经验,所以直接update。第一批5万条数据大约执行了4个小时,感觉等不下去了,看了一下执行计划,Cost 是10位数(当然,Cost 跟执行时长并无直接关系,只当作一个参考),如下图。

由执行计划可以看出,进行了两次全盘扫描,且提供数据的表中无任何索引。

二、SQL改进

1、添加索引

上文中提到提供数据的表中无任何索引,所以可以添加一个索引来缩短执行时长。在添加索引过后,Cost 直接降至9位数,如下图。

可以看出,一次执行了全表扫描和一次索引 Index Range Sacn。

2、Merge Into

Merge into 是 Oracle 9i 引入的功能,其语法如下:

MERGE INTO table_name alias1 
USING (table|view|sub_query) alias2
ON (join condition) 
WHEN MATCHED THEN 
    UPDATE table_name 
    SET col1 = col_val1, 
           col2 = col_val2 
WHEN NOT MATCHED THEN 
    INSERT (column_list) VALUES (column_values);

Oracle 中 Merge 语句原意是用来进行更新整张表使用的,是ETL工具比较常用的语法,重点是在Using上。Merge语法:在 alias2 中Select出来的数据,每一条都跟alias1进行 ON ( join condition )的比较,如果匹配,就进行更新的操作(Update),如果不匹配,就进行插入操作(Insert)。在一个同时存在Insert和Update语法的Merge语句中,总共Insert/Update的记录数,就是Using语句中alias2的记录数。

使用 Merge Into后查看执行计划:

可以看出使用Merge Into 确实可以快速更新。

3、第三次大概300w数据,其实前两次都涉及到Date类型字段的迁移或更新,此时如果直接to_char转换日期格式,可能会造成时间上的延长,可以再临时表中建一个同类型的字段,这是就不需要类型转换从而节省时间的开销。其次,如果有需要对目标字段加解密等操作,建议不要在执行语句时做,可以将数据迁移出后再进行加解密。再就是像几百万条数据,可以写个存储过程,提交 10w/次。

最后总结一下触发全表扫描的SQL关键词:

Select * from table

where 子句中使用 != 或 <> 操作符

没有在where 及 order by 涉及的列上建立索引

where 子句中对字段进行 null 值判断

在 where 子句中使用 or 来连接条件

前置百分号: select  custname from user where custname like ‘%abc%’

慎用 in 和 not in:如果是连续数值,可用 between 代替

where 子句中对字段进行表达式操作或函数操作

  • 0
    点赞
  • 11
    收藏
    觉得还不错? 一键收藏
  • 1
    评论
评论 1
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

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

抵扣说明:

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

余额充值