首页
学习
活动
专区
工具
TVP
发布
社区首页 >专栏 >EDB分区表的又一个“坑”

EDB分区表的又一个“坑”

作者头像
bisal
发布2019-01-29 15:24:33
6590
发布2019-01-29 15:24:33
举报

下周有一个应用上线,其中涉及一个夜维删除逻辑的应用,大体功能是按照时间删除一张表的历史数据,这张表的主键是另外一张时间分区表的外键,使用的是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一直所说的”由点及面“的学习方法,虽然有时会很痛苦,但会受益,至少不跟别人比,跟自己比是有提高的,也许这就够了,人的一生比来比去也就是一生,况且神人就是那么几位,大多数还是平凡之人,费了劲了还不及神人的一点功力,这里不是说就轻易放弃了,反正追不上,而是说要有豁达的胸怀,能为自己设立一个目标、一个榜样,即使现实中根本无法超越甚至赶上,但至少一直会提醒自己,他那么厉害,我差这么远,没有理由不努力,给自己暗示,就是让自己持续提高的动力,大家共勉。

本文参与 腾讯云自媒体分享计划,分享自作者个人站点/博客。
原始发表:2016年04月29日,如有侵权请联系 cloudcommunity@tencent.com 删除

本文分享自 作者个人站点/博客 前往查看

如有侵权,请联系 cloudcommunity@tencent.com 删除。

本文参与 腾讯云自媒体分享计划  ,欢迎热爱写作的你一起参与!

评论
登录后参与评论
0 条评论
热度
最新
推荐阅读
相关产品与服务
数据库
云数据库为企业提供了完善的关系型数据库、非关系型数据库、分析型数据库和数据库生态工具。您可以通过产品选择和组合搭建,轻松实现高可靠、高可用性、高性能等数据库需求。云数据库服务也可大幅减少您的运维工作量,更专注于业务发展,让企业一站式享受数据上云及分布式架构的技术红利!
领券
问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档