update merge 大表性能优化 index的作用巨大

1. 

update customers a 
   set (city_name, customer_type) =
       (select b.city_name, b.customer_type
          from tmp_cust_city b
         where b.customer_id = a.customer_id)
 where exists
 (select  /*+ use_nl(b ) */ 1 from tmp_cust_city b where b.customer_id = a.customer_id)

---这种优化，

A大表可能使用/*+ use_hash(b ) */ hash join将匹配的找出来，再根据customer_id去找b.下的记录，这样需要在b表的customer_id创建index---性能差距几百倍

如果a表很小，可以直接使用nest loop循坏更新，b表的customer_id也必须要创建index

/*+ use_nl(b ) */

 
近日，在项目中遇到一个需求。需要批量跑sql脚本。并且在脚本中，需要将A表中的数据根据B表的条件进行批量更新。但是，oracle中不支持update中使用left join等。

原因
Oracle没有update from语法，set前面只能存在一个表，逻辑只能在子查询中处理。

实现办法
1.子查询
写法一：
UPDATE table_1 a 
SET col_x1 = (SELECT b.col_y1, b.col_y2 FROM table_2 b WHERE b.col_n = a.col_m),
    col_x2 = (SELECT b.col_y2 FROM table_2 b WHERE b.col_n = a.col_m)
WHERE EXISTS (SELECT * FROM table_2 b WHERE b.col_n = a.col_m) ;

这其中table_2是同一张表，可以直接两列一起update，如果能在table_2将index 性能也可以，但是不能，这个sql性能很差

 
写法二：
UPDATE table_1 a
   SET (col_x1, col_x2) = (SELECT b.col_y1, b.col_y2 FROM table_2 b WHERE b.col_n = a.col_m) 这个（）要有
WHERE EXISTS (SELECT * FROM table_2 b WHERE b.col_n = a.col_m);

对于子查询的值只能是一个唯一值，不能是多值。
子查询在绝大多数情况下，最后面的where EXISTS子句是重要的，否则将得到错误的结果。且where EXISTS子句可用另一方法IN代替，如上。最后的子句是对a表被更新记录的限制，如无此句，对于a表中某记录，
如在b表中关联不到对应的记录,则该记录被更新字段将被更新为null。where EXISTS子句就是排除对a表中该情况的记录进行更新。

--如果A表巨大，B表也大，而且不能再B上建index 如何处理呢，可以先用with as将AB表join后的结果集取出来，再进行update，with as不能建index，所以，可以用临时表解决。 

上次的例子是原来20分钟语句使用with as改写是11秒，如果再用临时表改写是0.5秒。

三张表也是同样的道理，如果A表的2 3列分别修改为B C表的第二列，也可以将ABC关联起来，类似维度表和Fact表，再将数值赋上

2.利用视图
UPDATE (SELECT A.NAME ANAME,B.NAME BNAME FROM A,B WHERE A.ID=B.ID)
SET ANAME=BNAME;
1
2
注意：

对于视图更新的限制：
如果视图基于多个表的连接，那么用户更新（update）视图记录的能力将受到限制。除非update只涉及一个表且视图列中包含了被更新的表的整个主键，否则不能更新视图的基表。

ID是A表还是B表的主键不同，可能导致修改的结果不能， 没有主键10G之后报错。

3.存储过程
MERGE INTO T2
USING T1
ON (T2.A = T1.A)
WHEN MATCHED THEN
  UPDATE SET T2.C = T1.B

另外，Oracle中的Delete的from子句也没有多表级联删除的功能，只能通过子查询的方式来做：
delete from 表A where exists (select * from 表B where 表A.empid=表B.empid)

这两个开两个窗口同时改就可以修改A B表的匹配的记录
delete from 表B where 表B.empid in (select empid from 表A)

----------------------总结，

1.单条语句更新

语法：UPDATE 表名称 SET 列名称 = 新值 WHERE 列名称 = 某值

说明：如果更新的字段加了索引，更新时会重建索引，更新效率会慢。单表更新或较简单的语句采用使用此方案更优。

2.批量数据更新

语法：update 表a set a.字段1 = (select b.字段1 from 表b where a.字段2=b.字段2) where exists(select 1 from 表b where a.字段2=b.字段2)

说明：查表a的所有数据，循环每条数据，验证该条数据是否符合exists(select 1 from 表b where a.字段2=b.字段2)条件，如果是则执行(select b.字段1 from 表b where a.字段2=b.字段2)查询，查到对应的值更新a.字段1中。关联表更新时一定要有exists(select 1 from 表b where a.字段2=b.字段2)这样的条件，否则将表a的其他数据的字段1更新为null值。

3.merge更新法

语法：merge是oracle特有的语句，语法如下：

MERGE INTO table_name t1

USING (table|view|sub_query) t2

ON (join condition)

WHEN MATCHED THEN

UPDATE table_name

SET col1 = col_val1,

col2 = col2_val

WHEN NOT MATCHED THEN

INSERT (column_list) VALUES (column_values);

说明：在t2中Select出来的数据，每一条都跟t1进行 ON (join condition)的比较，如果匹配，就进行更新的操作(Update),如果不匹配，就进行插入操作(Insert)。执行merge不会返回影响的行数。Merge语句的写法比较繁琐，并且最多只能两个表关联，复杂的语句用merge更新将力不从心且效率差。两表关联且被更新表不是通过关联表主键关联的，采用此方案更优。

4.利用存储过程中游标更新

语法：

begin

for cur in (查询语句) loop –-循环

--更新语句(根据查询出来的结果集合)

end loop; --结束循环

end;

说明：oracle支持快速游标，不需要定义直接把游标写到for循环中，这样就方便了我们批量更新数据。再加上oracle的rowid物理字段(oracle默认给每个表都有rowid这个字段，并且是唯一索引)，可以快速定位到要更新的记录上。使用快速游标的好处很多，可以支持复杂的查询语句，更新准确，无论数据多大更新效率仍然高，但执行后不返回影响行数。

多表关联且逻辑复杂的，采用此方案更优。