Merge加Hint(Append)时出现的问题
来源:互联网 发布:淘宝店铺搜索网址 编辑:程序博客网 时间:2024/06/06 13:15
之前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中加。
问题就出在在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中加。
- Merge加Hint(Append)时出现的问题
- Merge加Hint(Append)时出现的问题
- arcgis的dissolve,append,Merge,merge union append
- Oracle append Hint(一)
- Oracle append Hint(二)
- Oracle append Hint(三)
- Oracle append Hint(四)
- 使用TortoiseSVN客户端Merge时出现的问题
- pandas中merge,append,concat的用法
- hint: merge 和 push_pred
- git push 时出现hint错误
- 不要乱加hint
- “+”与StringBuilder#append的问题
- svn merge 时候出现的问题以及解决方法
- 关于oracle数据库,表空间,表级的logging属性与hint append
- Insert 中 Append Hint 生效时间 实验
- 使用Hint时的注意事项
- leading() hint的用法,连接顺序问题
- Qt 的model和view part1
- Java math.round()
- 程序员找工作那点事儿
- 如何将原有代码转换为资源管理代码 ?
- WPF ListBox 横向排列
- Merge加Hint(Append)时出现的问题
- Oracle11g数据库记录
- SQL优化
- 详解Android源码的编译
- TweetRobots:Twitter批量注册工具!
- 求n!的4种方法
- Java读写Excel之POI超入门
- Eclipse中将web项目自动发布到Tomcat webapps下
- GeoServer + Openscales 的跨域问题