Chinaunix首页 | 论坛 | 博客
  • 博客访问: 1037773
  • 博文数量: 159
  • 博客积分: 3063
  • 博客等级: 中校
  • 技术积分: 2703
  • 用 户 组: 普通用户
  • 注册时间: 2012-04-01 01:51
文章分类

全部博文(159)

文章存档

2013年(48)

2012年(111)

分类: Oracle

2012-05-31 12:12:18

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

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


Table created.
Elapsed: 00:00:00.10
SQL> 


CREATE TABLE test_b(
  2  id NUMBER);

Table created.

Elapsed: 00:00:00.01
SQL>
SQL> INSERT INTO test_a VALUES(1,100);       
INSERT INTO test_a VALUES(2,200);
1 row created.

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

Elapsed: 00:00:00.00
SQL>

1 row created.

Elapsed: 00:00:00.00
SQL> INSERT INTO test_b VALUES(1);

1 row created.

Elapsed: 00:00:00.01
SQL> INSERT INTO test_b VALUES(2);

1 row created.

Elapsed: 00:00:00.00
SQL> INSERT INTO test_b VALUES(3);

1 row created.

Elapsed: 00:00:00.01
SQL> INSERT INTO test_b VALUES(4);

1 row created.

Elapsed: 00:00:00.01
SQL>
SQL> SELECT * FROM test_a ORDER BY 1;
SELECT * FROM test_b ORDER BY 1;
        ID      SCORE
---------- ----------
         1        100
         2        200
         3        300

3 rows selected.

Elapsed: 00:00:00.01
SQL>

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

4 rows selected.

Elapsed: 00:00:00.01

 

现在更新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.

Elapsed: 00:00:00.01

 

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

 

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

SQL> INSERT INTO test_b VALUES(3);

1 row created.

Elapsed: 00:00:00.00
SQL> COMMIT;

Commit complete.

Elapsed: 00:00:00.00
SQL> SELECT * FROM TEST_B ORDER BY 1;

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

5 rows selected.

Elapsed: 00:00:00.00

 

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.

Elapsed: 00:00:00.01
SQL> SELECT * FROM TEST_A;

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

3 rows selected.

Elapsed: 00:00:00.00

 

总结:使用了该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.

Elapsed: 00:00:00.00
SQL> SELECT * FROM TEST_A;

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

3 rows selected.

Elapsed: 00:00:00.01

 

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

 

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

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

阅读(1756) | 评论(0) | 转发(0) |
给主人留下些什么吧!~~