ora-01779错误

ora-01779错误

在对update进行优化时，考虑到update from 效率比较高，但由于Oracle会对键值进行检查，导致报ora-01779错误，通过采用BYPASS_UJVC跳过Oracle的键的判定，达到同样的效果。

例子如下：

update (select   /*+ BYPASS_UJVC */
                t1.drawref,
                t.oprtype,
                t.bl_validation,
                'F' bl_validation_new,
                t.bl_msg,
                t.bl_msg ||
                case when (t1.drawref is null and t.oprtype = 'U') then  '交易帐户编号不存在,不能更新;'                                                                  
                     when (t1.drawref is null and t.oprtype = 'D') then  '交易帐户编号不存在,不能删除;'                                                                  
                     when (t1.drawref is not null and t.oprtype = 'I') then '交易帐户编号已存在,不能插入;'                                                               
                 end bl_msg_new    
        from  s_bl2_con_pro_temp t
         left join s_bl2_con_pro t1
            on t.drawref = t1.drawref
         where
         --t.bl_user =134
            (t.OPRTYPE <> 'N' OR t.OPRTYPE is null) 
           and  t1.BL_DELETED(+) = 'F'
           and t1.OPRTYPE(+) in ('U','D','I')
           and t1.partition_key(+) = '201202290000'
           and t.partition_key = '201202290000'
        )
   set bl_validation = bl_validation_new,                                                                                                              
       bl_msg = bl_msg_new
;

commit;

具体说明如下：

 在我们肯定当前条件能确定记录唯一，又不是主键的时候，可以采用两种方法处理。

1、创建唯一性索引

2、update ( select /*+ BYPASS_UJVC */ a.cola va,b.colb vb from a,b where a.id=b.id) set va=vb

 BYPASS_UJVC的作用是跳过Oracle的键的判定。