关联UPDATE的各种方法总结

oracle更新语法：
1.一般语法
   update tab set col = .... [where ...]   =后可以有子查询，但是必须对于tab的每一列返回唯一一行与之对应，where是需要更新的表，部分更新必须加，否则相关子查询的更新会把没有匹配的更新为null,如:
  update tab a set a.col=(select b.col from b where a.id=b.id)
   where exists (select 1 from b  where a.id=b.id) 

2.改进语法merge
  merge into tab
  using (表|视图|子查询等)  --子查询需要加括号
  when match then
   do update
  when no match then
  do insert                               

    insert语法和update语法有所不同，详细参考文档，10g还支持update,insert的有条件更新和插入，支持update的delete where,支持只有update或insert的
不能修改using里的关联列，同样，必须每一行有唯一与之对应的

   上面两种语法如果找不到唯一对应的，需要改进语句，比如加rownum=1

3.update inline view的用法
   update (select ...........关联查询) set 目标=源
  如 update(select a.name,b.name from a,b where a.id=b.id) set a.name=b.name;
  需要unique建保证唯一对应，比如上面的必须要b.id有唯一键，也就是preserved key,比如唯一索引什么的都可以，11g之前可以用  hint: bypass_ujvc，这样不需要唯一键，但是可能有问题，一对多会更新多次，11g这个hint失效
  delete (select ....) 也可以，有很多要求，可以看sql文档，insert (select ...)限制更多
  第3种方法来源于可更新的视图

    oracle更新基本有3种sql写法，后面两种往往优化中会使用到，特别第一种的更新关联子查询中源表不走索引，那么更新很多，相当于 nested loop,肯定慢，而且还有个where过滤，多次访问源表。

     如果是基于一个表的，那么可以更新插入，删除，当然也得满足对视图DML的要求，比如rownum等，没有preserved key要求
 UPDATE (SELECT * FROM t1) SET NAME='x' WHERE ID=1;
  UPDATE (SELECT NAME FROM t1) SET NAME='x' WHERE NAME='m';
 DELETE FROM (SELECT * FROM t1) WHERE ID=1

 视图DML要求:
连接视图（join view）指在一个视图的定义查询的 FROM 字句中引用了多个
表或视图（即存在连接（join）），且查询中没有使用以下子句：
DISTINCT，聚合函数（aggregation），GROUP BY，START WITH，
CONNECT BY，ROWNUM，以及集合操作（set operation）(UNION ALL，
INTERSECT 等等)。

而可更新的连接视图（updatable join view ）是指能够执行 UPDATE，
INSERT，和 DELETE 操作的连接视图（join view）。
ALL_UPDATABLE_COLUMNS，DBA_UPDATABLE_COLUMNS，及
USER_UPDATABLE_COLUMNS 数据字典视图（data dictionary view）中的信
息描述了视图中那些列是可更新的。为了保证视图是可更新的，其定义中不
能包含以下语法结构（construct）：
● 集合操作符（set operator）
● DISTINCT 操作符
● 聚合函数（aggregate function）或分析型函数（analytic function）
● GROUP BY，ORDER BY，CONNECT BY，或 START WITH 字句
● 在 SELECT 之后的列表中使用collection expression
● 在 SELECT 之后的列表中使用子查询（subquery）
● 连接（join）（但是有例外情况）

对于不可更新的视图，可以利用 INSTEAD OF 触发器（trigger）对其数据进行
修改。还要遵守preserved key要求。

下面看下具体例子：


1)对于关联UPDATE的操作，类似于FILTER,标量子查询等，也有缓存，FILTER第一个Child的每行驱动其它Child（如果出现类似:B1的，就会被驱动执行多次），这种优化得类似:B1的走高效索引，执行次数不可太多。
2)ID=1的UPDATE需要更新14999行，驱动ID=5全表扫描NEW_TAB次数上万次，效率低。
3)当然，这里字段ID唯一性很强，可以建立UNIQUE INDEX/普通INDEX，这样第5步就可以走索引了。见下面演示如何不建索引搞定这种关联UPDATE。

     使用MERGE写法、UPDATE INLINE VIEW写法实现关联UPDATE，执行计划减少了表访问数目和次数，并且可高效使用HASH JOIN算法。
   

   对于UPDATE INLINE VIEW需要preserved key支持，11g之前可以使用UJVC_BYPASS HINT。