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 子句中对字段进行表达式操作或函数操作