日期:2014-05-16  浏览次数:20555 次

加字段然后批量更新
本次的业务需求是:在一张旧表t_bnet_customer上增加了一个新的字段,这个字段在值在另一张表t_customer_extend里面,一一对应来更新;

原先的语句:

UPDATE t_bnet_customer a
    SET a.customer_id = (SELECT  b.cust_nbr FROM t_customer_extend b
    WHERE a.id = b.bnet_id )
    WHERE EXISTS (SELECT  b.cust_nbr FROM t_customer_extend b
    WHERE a.id = b.bnet_id)

后来的语句:

update (
select a.customer_id a1, b.cust_nbr b1
from t_bnet_customer   a,
t_customer_extend b
where a.id = b.bnet_id
)  set a1 = b1  ;

上面的语句被oracle限制了,不能执行的话,换成下面的:

update (
select /*+ BYPASS_UJVC */ a.customer_id a1, b.cust_nbr b1
from t_bnet_customer   a,
t_customer_extend b
where a.id = b.bnet_id
)  set a1 = b1  ;

---------------------------------原因如下---------------------------------

http://lastcaress.blog.sohu.com/149585028.html

用implicit view update的时候,如果源表没加唯一性约束,会碰到

ORA-01779: cannot modify a column which maps to a non key-preserved table

比如用下面的pzl_test_1 b的col2去更新pzl_test a的col2,用两表的col1关联



SQL> select col1,col2 from pzl_test;

COL1        COL2
--------------------------------------------
1           A

2           B

3           C



SQL> select col1,col2 from pzl_test_1 b;

COL1        COL2
--------------------------------------------
2           BB

3           CC

4           DD



如果b表的col1字段没有唯一约束,执行

SQL> update

  2  (select a.col2 col2a,b.col2 col2b

  3  from pzl_test a,

  4  pzl_test_1 b

  5  where a.col1=b.col1

  6  )

  7  set col2a=col2b;



结果是:

ERROR at line 7:
ORA-01779: cannot modify a column which maps to a non key-preserved table



然后给b表加上唯一性约束:

SQL> create unique index idx_pzl_test_1 on pzl_test_1(col1);

Index created.



再执行之前的update语句,就可以成功了

SQL> 省略..
2 rows updated.



查看a表结果:

SQL> select col1,col2 from pzl_test;

COL1        COL2
--------------------------------------------
1           A

2           BB

3           CC



显示正常。



先rollback了它。

SQL> rollback;

Rollback complete.



ORACLE要求b表关联字段唯一是合理的,否则更新a表时,如果b表有多条记录对应,不知道用哪条记录去更新才好。

但在实际开发时,经常会碰到虽然逻辑上能保证b表的关联字段唯一,但不想,或者无法给它加唯一性约束的情况(它是唯一,却不能给它一个名分。。)


以前碰到这样的情况,我是用update a + 两句子查询或者先过滤b表再merge来实现的。但这样对b表的访问次数是implicit view update的2倍,降低了效率。(10g以上版本可以用一句merge实现和view update相同的效果,但我们最大的两个数据库都是9i

昨天晚上逛街时发现,原来有个hint可以强制oracle跳过唯一检查,在b表没有建唯一约束的情况下也能够用implicit view update

这就是/*+BYPASS_UJVC*/




把b表的唯一索引干掉:

SQL> drop index idx_pzl_test_1;

Index dropped.



再执行前面的update语句(加上hint):



SQL> upda