ora-01779 non-key-preserved table错误及/*+ BYPASS_UJVC*/ 的使用技巧

update (select a.cola va,b.colb vb from a,b where a.id=b.id) set va = vb

很喜欢这种更新方式，但有时限制很讨厌，常常抛出ora-01779的异常，原因是oracle要做唯一性的判定。

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

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的键的判定。

来源：http://hi.baidu.com/zhaoyh603/blog/item/f09247103137c60a203f2e52.html

-----------------------------------------------------------------------------------------------------------------------------------------------

在使用implict update table 时，发现其中一个表一定要有唯一约束，否则会报错！但是oracle可以使用hints：/*+ BYPASS_UJVC*/ 屏蔽掉唯一性的检查。具体测试过程如下：

SQL> CREATE TABLE test_a(
     2    id NUMBER ,
     3    score NUMBER );

Table created.

SQL>  CREATE TABLE test_b(
     2     id NUMBER);

Table created.

SQL> INSERT INTO test_a VALUES(1,100);  

1 row created.

SQL> INSERT INTO test_a VALUES(2,200);
1 row created.

SQL> INSERT INTO test_a VALUES(3,300);
1 row created.

SQL>

 

SQL> INSERT INTO test_b VALUES(1);

1 row created.

SQL> INSERT INTO test_b VALUES(2);

1 row created.

SQL> INSERT INTO test_b VALUES(3);

1 row created.

SQL> INSERT INTO test_b VALUES(4);

1 row created.

SQL> SELECT * FROM test_a ORDER BY 1;

        ID      SCORE
---------- ----------
         1        100
         2        200
         3        300

3 rows selected.

SQL> SELECT * FROM test_b ORDER BY 1;

        ID
----------
         1
         2
         3
         4

4 rows selected.
 

现在更新a表的字段：（先不考虑SQL的功能，只是测试这种方法）

SQL> UPDATE (
     2    SELECT  a.id,a.score,b.id AS b_id
     3    FROM test_a a,
     4                test_b b
     5    WHERE a.id=b.id
     6    )
     7    SET id=b_id;
SET id=b_id
    *
ERROR at line 7:
ORA-01779: cannot modify a column which maps to a non key-preserved table

 

报错了，需要对b表加唯一索引。加入hints强制执行

SQL> UPDATE (
     2    SELECT/*+ BYPASS_UJVC*/  a.id,a.score,b.id AS b_id
     3    FROM test_a a,
     4                test_b b
     5    WHERE a.id=b.id
     6    )
     7    SET id=b_id;

3 rows updated.
 

说明：oracle可以跳过检查唯一约束。

 

继续往b表添加一条记录，使b表的记录不唯一。

SQL> INSERT INTO test_b VALUES(3);

1 row created.

SQL> COMMIT;

Commit complete.

SQL> SELECT * FROM TEST_B ORDER BY 1;

        ID
----------
         1
         2
         3
         3
         4

5 rows selected.
 

SQL> UPDATE (
     2    SELECT/*+ BYPASS_UJVC*/  a.id,a.score,b.id AS b_id
     3    FROM test_a a,
     4                test_b b
     5    WHERE a.id=b.id
     6    )
     7    SET id=b_id;

4 rows updated.

SQL> SELECT * FROM TEST_A;

        ID      SCORE
---------- ----------
         1        100
         2        200
         3        300

3 rows selected.
 

总结：使用了该hints，oracle就完全放弃了检查唯一性。因此SQL可以执行通过。

 

但要注意，是否能达到我们希望的目的，例如：下面进行一个有意义的更新，将a表中只要id在b表中出现就更新score，将score加1.

执行SQL如下：

SQL> UPDATE (
     2    SELECT/*+ BYPASS_UJVC*/  a.id,a.score,b.id AS b_id
     3    FROM test_a a,
     4                test_b b
     5    WHERE a.id=b.id
     6    )
     7    SET score=score+1;

4 rows updated.

SQL> SELECT * FROM TEST_A;

        ID      SCORE
---------- ----------
         1        101
         2        201
         3        302

3 rows selected.
 

发现对于id=3的记录多加了个1。因此并没有得到我们想要的结果。这种情况应该先对b表的id进行去重。然后再更新。(其实完全也可以换种写法，呵呵，在此就不写了）

 

总结：虽然对于此种应用，oracle跳过了检查唯一性，但是我们要注意在具体使用时，是否真的就达到了我们的效果！

            要巧用并且活用现在已经有的功能。

 

来源：http://blog.csdn.net/ningjieshuijing/article/details/5850208

----------------------------------------------------------------------------------------------------------------------------------------------------------------

在对update操作进行性能优化过程中，修改原来普通的修改语句，采用多表连合修改的方式，诸如：
update (select /*+ BYPASS_UJVC */ name , rname
    from table1 t1, table2 t2
 where t1.id = t2.id
     and t1.id is not null)
      set name = rname
的写法，性能确实有所提高。

来源：http://topic.csdn.net/u/20081126/22/0cb3c3c3-131b-4487-9aa0-cf265b34c202.html