Merge加Hint(Append)时出现的问题

 

Merge加Hint(Append)时出现的问题

url:http://hi.baidu.com/klkl1110/blog/item/3bbc89c27c440652b219a86c.html

2008-11-04 11:39

之前count(*)的问题困扰了team很久，甚至准备到Metalink上开TAR了。没想到昨天这个问题被深受其害的开发组的小MM给重现了，至此这个很妖的问题以及由此引起的另一个在逃的问题被缉拿归案了:
问题就出在在Mergez上用hint, --- Merge /*+ APPEND */ ，由它引起的问题如下：
1. 查询被Merge的target表如果建pk索引时，count(*)始终为0(见上一篇文章)
2. target表上的pk约束和unique index失效，但通过dba_constraints,dba_indexes看状态，没有问题;

--- 实验过程如下:
Connected.
kl@k01> DROP TABLE KL_TEST PURGE;

Table dropped.

(创建目标table)
kl@k01> create table KL_TEST    
2 (
3    a           NUMBER(15) not null,
4    b           NUMBER(15) not null,
5    c           NUMBER(15) not null,
6    d           NUMBER(15) not null,
7    e           NUMBER(15) not null,
8    f           NUMBER(15) not null
9 )
10 ;

Table created.

(创建PK,这是必须场景)
kl@k01>
kl@k01> alter table KL_TEST
2    add constraint KL_TEST_PK primary key (A, B, C, D)
3    ;

Table altered.

kl@k01>
kl@k01> DROP TABLE KL_TEST_1 PURGE;

Table dropped.
(创建source表，里面的数据将被Merge进target表KL_TEST)
kl@k01> CREATE TABLE KL_TEST_1 NOLOGGING PCTFREE 0 AS
2 SELECT * FROM KL_TEST
3 WHERE 1=2;

Table created.

kl@k01>
kl@k01> INSERT INTO KL_TEST_1 VALUES (1,1,1,1,1,1);

1 row created.

kl@k01> INSERT INTO KL_TEST_1 VALUES (1,2,1,2,1,2);

1 row created.

kl@k01> INSERT INTO KL_TEST_1 VALUES (1,1,1,1,1,1); --- 故意在source table中加入了一条重复纪录

1 row created.

kl@k01>
kl@k01> commit;

Commit complete.

kl@k01>
kl@k01>
(开始merge,hint是关键)
kl@k01> MERGE /*+ APPEND */ INTO KL_TEST trgt
2 USING
3 (select fct.a,fct.b,fct.c,fct.d,fct.e,fct.f from KL_TEST_1 fct) tmp
4 ON
5 (tmp.a = trgt.a
6   and tmp.b = trgt.b
7   and tmp.c = trgt.c
8   and tmp.d = trgt.d
9   and tmp.e = trgt.e
10   and tmp.f = trgt.f)
11 WHEN NOT MATCHED THEN
12 INSERT (a,b,c,d,e,f)
13 VALUES (tmp.a,tmp.b,tmp.c,tmp.d,tmp.e,tmp.f);

3 rows merged.

kl@k01>
kl@k01> commit;

Commit complete.

kl@k01>
(察看COUNT(*), 没纪录!!!)
kl@k01> SELECT COUNT(*) FROM KL_TEST;

COUNT(*)
----------
         0
(强制FULL，有3条! 问题1出现了)
kl@k01> SELECT /*+ FULL (KL_TEST)*/ COUNT(*) FROM KL_TEST;

COUNT(*)
----------
         3
(察看纪录，有一条重复纪录，PK&UNIQUE INDEX呢？问题2出现了)
kl@k01> select * from kl_test;

         A          B          C          D          E          F
---------- ---------- ---------- ---------- ---------- ----------
         1          1          1          1          1          1
         1          1          1          1          1          1
         1          2          1          2          1          2

(PK是好的！)
kl@k01> select CONSTRAINT_NAME,CONSTRAINT_TYPE, STATUS from dba_constraints where table_name ='KL_TEST';

CONSTRAINT_NAME                                                                            CON STATUS
------------------------------------------------------------------------------------------ --- -----------------
SYS_C00141098                                                                              C   ENABLED
SYS_C00141093                                                                              C   ENABLED
SYS_C00141094                                                                              C   ENABLED
SYS_C00141095                                                                              C   ENABLED
SYS_C00141096                                                                              C   ENABLED
SYS_C00141097                                                                              C   ENABLED
KL_TEST_PK                                                                                 P   ENABLED

7 rows selected.

(居然还能insert进去，妖吧！)
kl@k01> insert into kl_test values (1,1,1,1,1,1);

1 row created.

kl@k01> commit;

Commit complete.

kl@k01> select * from kl_test;

         A          B          C          D          E          F
---------- ---------- ---------- ---------- ---------- ----------
         1          1          1          1          1          1
         1          1          1          1          1          1
         1          1          1          1          1          1
         1          2          1          2          1          2

---- 看了上面这些，看来Oracle新的功能bug还是不少，还是老老实实的按常规方法吧，去掉Hint -append，再看看:
kl@k01> MERGE INTO KL_TEST trgt
2 USING
3 (select fct.a,fct.b,fct.c,fct.d,fct.e,fct.f from KL_TEST_1 fct) tmp
4 ON
5 (tmp.a = trgt.a
6   and tmp.b = trgt.b
7   and tmp.c = trgt.c
8   and tmp.d = trgt.d
9   and tmp.e = trgt.e
10   and tmp.f = trgt.f)
11 WHEN NOT MATCHED THEN
12 INSERT (a,b,c,d,e,f)
13 VALUES (tmp.a,tmp.b,tmp.c,tmp.d,tmp.e,tmp.f);
MERGE INTO KL_TEST trgt
*
ERROR at line 1:
ORA-00001: unique constraint (KL.KL_TEST_PK) violated --(开始检查PK约束了)

--- Count(*)问题也正常了。

所以宗上，Merge加hint时要慎重，如果一定要用hint建议还是在存储过程中的insert/update/select中加。