下周有一个应用上线,其中涉及一个夜维删除逻辑的应用,大体功能是按照时间删除一张表的历史数据,这张表的主键是另外一张时间分区表的外键,使用的是EDB(9.2)数据库,这次测试就意外发现了一个说是隐藏,也不算隐藏,至少和Oracle分区表有很大不同的地方,或者可以称他为KENG,“坑”。
P.S. 这里不是贬低EDB,毕竟能做到和Oracle最相近并不容易。但就分区这个功能,已经不是第一次碰见“坑”了,可能对EDB来说不公平,也许这个功能就是这么设计的,但至少从Oracle使用习惯上来看,确实需要额外注意。
上次碰见的问题可以参考:http://blog.csdn.net/bisal/article/details/50089359
在这还要感谢wc和lx同学的帮助,一块排查。
实验过程:
目标:T1表是主表,T2表是子表,T2表的t1_id字段作为外键关联T1表的主键id字段。要验证的就是是否可以在主子表有关联数据的情况下,直接删除T1表的数据。
1.按照正常理解,这种操作是不行的,EDB普通表是支持的,实验如下。
(1) 创建T1和T2表
create table t1 ( id number primary key); create table t2 ( id number primary key, t1_id number, constraint fk_t2 foreign key(t1_id) references t1(id));
(2) 插入测试数据
insert into t1 values(1); insert into t2 values(1, 1); commit;
此时T1和T2存在关联数据。
(3) 此时直接删除T1表记录,会报错:
[SQL]delete from t1; [Err] ERROR: update or delete on table "t1" violates foreign key constraint "fk_t2" on table "t2" DETAIL: Key (id)=(1) is still referenced from table "t2".
因为存在外键关联,不能先删除主表记录。关系型数据库都会有这样的要求。
同样,直接drop表T1也是禁止的,
[SQL]drop table t1; [Err] ERROR: cannot drop table t1 because other objects depend on it DETAIL: constraint fk_t2 on table t2 depends on table t1 HINT: Use DROP ... CASCADE to drop the dependent objects too.
2.接下来就要说我碰见的问题了。
(1) 创建主表T1和子表T2,唯一不同的是T2是分区表。
create table t1 ( id number primary key); create table t2 ( id number primary key, t1_id number, t2_date date, constraint fk_t2 foreign key(t1_id) references t1(id)) partition by range (t2_date) ( partition part_01 values less than ('2016-01-02'), partition part_02 values less than ('2016-01-03'), partition part_03 values less than ('2016-01-04'), partition part_04 values less than ('2016-01-05') );
(2) 插入测试数据
insert into t1 values(1); insert into t1 values(2); commit; insert into t2 values(1, 1, to_date('2016-01-01','yyyy-mm-dd')); insert into t2 values(2, 1, to_date('2016-01-02','yyyy-mm-dd')); commit;
同样,表T1和T2存在关联数据。
(3) 此时删除T1表记录,
[SQL]delete from t1; 时间: 0.004s 受影响的行: 0
竟然能删除。。。第一次碰见还是比较的毁三观。。。为什么?
原因知道后其实很简单,但确实要是开始不了解EDB分区和Oracle分区的一些不同之处,很难绕出来。
之所以有主外键,就是为了让数据库能控制这种关联关系,这里能直接删除主表记录,并不是违反了主外键的逻辑原理,而是其实这块是不受主外键的约束,从建表后的DDL语句可以看出端倪。
这是子表定义,可以看见他有Foreign Key的定义:
CREATE TABLE "t2" ( "id" numeric NOT NULL, "t1_id" numeric, "t2_date" timestamp(6), CONSTRAINT "t2_pkey" PRIMARY KEY ("id"), CONSTRAINT "fk_t2" FOREIGN KEY ("t1_id") REFERENCES "btracer"."t1" ("id") ON DELETE NO ACTION ON UPDATE NO ACTION ) WITH (OIDS=FALSE)
但我们看其中一个分区表T2_PART_01的定义,却没有Foreign Key的定义,因此可以直接删除主表T1的记录是正常的,因为T2_PART_01根本没有和主表T1的主外键关联关系。
CREATE TABLE "btracer"."t2_part_01" ( "id" numeric NOT NULL, "t1_id" numeric, "t2_date" timestamp(6), CONSTRAINT "t2_part_01_pkey" PRIMARY KEY ("id"), CONSTRAINT "t2_part_01_partition" CHECK (t2_date < '02-JAN-16 00:00:00'::timestamp without time zone) ) INHERITS ("btracer"."t2") WITH (OIDS=FALSE) ;
其实开始使用EDB分区的时候,手工创建新分区的时候,都会为每个分区创建一个本地/局部索引,换句话说,本地/局部索引是需要手工创建的,而在Oracle中,这种索引是Oralce会自动创建。
create table t2 ( id number primary key, t1_id number, t2_date DATE) partition by range (t2_date) ( partition part_01 values less than (to_date('2016-01-02','yyyy-mm-dd')), partition part_02 values less than (to_date('2016-01-03','yyyy-mm-dd')), partition part_03 values less than (to_date('2016-01-04','yyyy-mm-dd')), partition part_04 values less than (to_date('2016-01-05','yyyy-mm-dd')) ); CREATE INDEX idx_t2 ON t2(t1_id) LOCAL; INSERT INTO t2 VALUES(1, 1, to_date('2016-01-01','yyyy-mm-dd')); INSERT INTO t2 VALUES(2, 1, to_date('2016-01-02','yyyy-mm-dd')); INSERT INTO t2 VALUES(3, 1, to_date('2016-01-03','yyyy-mm-dd')); INSERT INTO t2 VALUES(4, 1, to_date('2016-01-04','yyyy-mm-dd')); COMMIT; SELECT index_name,partition_name from user_ind_partitions WHERE INDEX_name = 'IDX_T2';
以此类推,约束也是需要为每个分区手工创建,不是一劳永逸的事情。
接下来,我们为每个分区表新建外键约束:
alter table t2_part_01 add constraint fk_t2_part_01 foreign key(t1_id) references t1(id); alter table t2_part_02 add constraint fk_t2_part_02 foreign key(t1_id) references t1(id); alter table t2_part_03 add constraint fk_t2_part_03 foreign key(t1_id) references t1(id); alter table t2_part_04 add constraint fk_t2_part_04 foreign key(t1_id) references t1(id);
再次删除T1表,
[SQL]delete from t1; [Err] ERROR: update or delete on table "t1" violates foreign key constraint "fk_t2_part_01" on table "t2_part_01" DETAIL: Key (id)=(1) is still referenced from table "t2_part_01".
此时返回的报错,就是主外键约束的问题了。
总结:
1.从EDB分区表这个问题上,至少可以看出Oracle和EDB两种不同数据库在处理分区表上的区别,每个人都有他自己的看法,怎么设计可能都有他考虑的角度,可以说无所谓对错,只是在易用性和接受性方面会有不同。
2.要学会触类旁通,说起来容易做起来难,如果知道EDB分区索引需要手工建立,能不能想到外键约束亦是如此?这就是能不能理解数据库原理精髓的能力,至少我还欠缺着。
3.都说实践是检验真理的唯一标准,在Oracle的世界里,实践就是实验,纸上谈兵不能解决问题,实验过程中可能又会因为一个问题碰到另一个问题,也许这就是eygle一直所说的”由点及面“的学习方法,虽然有时会很痛苦,但会受益,至少不跟别人比,跟自己比是有提高的,也许这就够了,人的一生比来比去也就是一生,况且神人就是那么几位,大多数还是平凡之人,费了劲了还不及神人的一点功力,这里不是说就轻易放弃了,反正追不上,而是说要有豁达的胸怀,能为自己设立一个目标、一个榜样,即使现实中根本无法超越甚至赶上,但至少一直会提醒自己,他那么厉害,我差这么远,没有理由不努力,给自己暗示,就是让自己持续提高的动力,大家共勉。