第一章 场景分析
众所周知，分区表的一个很实用的特性就是“高效的分区消除”，可以高效的对历史数据转移或删除。当实际环境中一个大表计划改造为分区表时，如果该表上又存在主键约束，同时其他表还建立了引用该主键的外键约束。那我们对主键表的主键应该怎样调整呢？
一般情况下，存在主外键的父表要改造为分区表时，会存在如下两种情况：
对父表主键列做分区条件，子表直接引用父表主键值；
对父表主键列之外的列，做为分区条件，如用日期列做范围分区，子表引用父表。
下面会分别对两种情况做测试验证。
第二章 对主键列直接分区
主键作为全局唯一索引
建表语句如下：
--创建父表
create table test_par1
(tdate DATE primary key)
partition by range (tdate) --主键分区
( partition p1 values less than (TO_DATE('20200201','YYYYMMDD')),
partition p2 values less than (TO_DATE('20200301','YYYYMMDD')),
partition pm values less than (MAXVALUE)) ;
--创建子表
create table test_par1_FKT
(tdate DATE );
--定义外键
ALTER TABLE test_par1_FKT ADD CONSTRAINT FK_PAR1_DATE FOREIGN KEY(tdate ) REFERENCES test_par1(tdate);
插入测试数据：
insert ALL
into test_par1
into test_par1_FKT
select sysdate from dual
union select sysdate-10 from dual
union select sysdate -40 from dual；
commit;
查看数据
SELECT t1.tdate t1date,t2.tdate t2date FROM test_par1 t1,test_par1_FKT t2 where t1.tdate=t2.tdate;
select table_name,partition_name,num_rows from dba_tab_partitions where table_name like 'TEST_PAR1';
下面做分区消除的测试：
1.删除数据：
TRUNCATE分区
DELETE父表数据：
DELETE FROM test_par1;
可以看到，由于存在外键的关系，无法对父表做分区数据TRUNCATE及删除操作。
设置外键的级联删除属性：
ALTER TABLE test_par1_FKT drop CONSTRAINT FK_PAR1_DATE ;
ALTER TABLE test_par1_FKT ADD CONSTRAINT FK_PAR1_DATE FOREIGN KEY(tdate ) REFERENCES test_par1(tdate) on delete cascade;
DELETE FROM test_par1 where tdate<sysdate-1;
可以看到，删除父表数据可以级联删除子表；
空数据的分区可以做TURNCATE操作。但存在主外键约束且分区中存在数据的部分，仍然无法实现TRUNCATE。
为了利用“高效的分区消除”特性。即对分区做DDL操作，比如快速DROP分区或TRUNCATE分区，实现空间回收。需要按照如下步骤来分别实现：
2.禁用外键约束：
alter table test_par1_FKT disable constraint FK_PAR1_DATE;
3.删除子表数据：
delete from test_par1_FKT
where exists(select 1 from test_par1 where test_par1.tdate=test_par1_FKT.Tdate
and test_par1.tdate=xxx /* 期望清理的主表分区条件 */);
commit; （注：如果与删除父表分区是不同的会话，需要提交。所以，这里提交，我认为更安全和稳妥。）
4.父表做分区清理：
alter table test_par1 truncate partition pm;
5.启用子表外键约束：
alter table test_par1_FKT enable constraint FK_PAR1_DATE;
6.查看状态：
我们发现，由于主键索引是全局索引，做分区TRUNCATE时导致了主键索引的失效，因此还需要在分区TRUNCATE时添加 update global indexes参数或重建索引：
alter index SYS_C0011758 rebuild;
经过测试，我们发现对主键全局索引直接分区并改造为分区表时，为了高效的迁移父表数据，需要对约束做一系列启停的操作，且子表的维护需要单独删除或转移数据。另外父表的主键索引会失效，因此也需要重建主键索引。针对主键全局索引时维护的成本过大。
主键作为分区普通索引
建表语句如下：
--创建父表
create table test_par2
(tdate DATE )
partition by range ( tdate )
( partition p1 values less than (TO_DATE('20200201','YYYYMMDD')),
partition p2 values less than (TO_DATE('20200301','YYYYMMDD')),
partition pm values less than (MAXVALUE)) ;
create index idx_part2_tdate on test_par2(tdate) local; --本地分区索引
alter table test_par2 add constraint pk_par2_tdate primary key(tdate);
--创建子表
create table test_par2_FKT(tdate DATE );
--定义外键
ALTER TABLE test_par2_FKT ADD CONSTRAINT FK_PAR2_DATE FOREIGN KEY(tdate ) REFERENCES test_par2(tdate) on delete cascade;
插入测试数据：
insert ALL
into test_par2
into test_par2_FKT
select sysdate from dual
union select sysdate-10 from dual
union select sysdate -40 from dual；
commit;
查看数据；
SELECT t1.tdate t1date,t2.tdate t2date FROM test_par1 t1,test_par1_FKT t2 where t1.tdate=t2.tdate;
select table_name,partition_name,num_rows from dba_tab_partitions where table_name like 'TEST_PAR1';
下面做分区消除的测试：
1.删除数据：
TRUNCATE分区
DELETE父表数据：
delete from test_par2 where tdate<sysdate-30;
由于设置了子表的外键级联删除，子表记录可以级联删除。
为了利用“高效的分区消除”特性。需要按照如下步骤来分别实现：
2.禁用外键约束：
alter table test_par2_FKT disable constraint FK_PAR2_DATE;
3.删除子表对应父表中的关联数据：
delete from test_par2_FKT
where exists(select 1 from test_par2 partition(pm) t1 where t1.tdate=test_par2_FKT.Tdate
and t1.tdate=xxx /* 期望清理的主表分区条件 */ );
commit; （注：如果与删除父表分区是不同的会话，需要提交。所以，这里提交，我认为更安全和稳妥。
4.父表做分区清理：
alter table test_par2 truncate partition pm;
5.启用子表外键约束：
alter table test_par2_FKT enable constraint FK_PAR2_DATE;
6.查看状态：
我们发现，由于主键索引是本地分区索引，做分区TRUNCATE时不会造成索引失效。
经过测试，我们发现对主键分区普通索引直接分区并改造为分区表时，为了高效的迁移父表数据，需要对约束做一系列启停的操作，且子表的维护需要单独删除或转移数据。但由于是本地索引不会造成索引的失效问题。针对主键本地索引的情况，维护的成本主要来自于子表数据的维护。
主键作为分区唯一索引
建表语句如下：
--创建父表
create table test_par3
(tdate DATE ,
constraint idx_part3_tdate primary key (tdate) using index local)
partition by range ( tdate )
( partition p1 values less than (TO_DATE('20200201','YYYYMMDD')),
partition p2 values less than (TO_DATE('20200301','YYYYMMDD')),
partition pm values less than (MAXVALUE)) ;
--创建子表
create table test_par3_FKT(tdate DATE );
--定义外键
ALTER TABLE test_par3_FKT ADD CONSTRAINT FK_PAR3_DATE FOREIGN KEY(tdate ) REFERENCES test_par3(tdate) on delete cascade;
插入测试数据：
insert ALL
into test_par3
into test_par3_FKT
select sysdate from dual
union select sysdate-10 from dual
union select sysdate -40 from dual；
commit;
查看数据；
SELECT t1.tdate t1date,t2.tdate t2date FROM test_par3 t1,test_par3_FKT t2 where t1.tdate=t2.tdate;
select table_name,partition_name,num_rows from dba_tab_partitions where table_name like 'TEST_PAR3';
下面做分区消除的测试：
1.删除数据：
TRUNCATE分区
DELETE父表数据：
delete from test_par3 where tdate<sysdate-30;
由于设置了子表的外键级联删除，子表记录可以级联删除。
为了利用“高效的分区消除”特性。需要按照如下步骤来分别实现：
2.禁用外键约束：
alter table test_par3_FKT disable constraint FK_PAR3_DATE;
3.删除子表对应父表中的关联数据：
delete from test_par3_FKT
where exists(select 1 from test_par3 partition(pm) t1 where t1.tdate=test_par3_FKT.Tdate
and t1.tdate=xxx /* 期望清理的主表分区条件 */);
commit; （注：如果与删除父表分区是不同的会话，需要提交。所以，这里提交，我认为更安全和稳妥。
4.父表做分区删除：
alter table test_par3 truncate partition pm;
5.启用子表外键约束：
alter table test_par3_FKT enable constraint FK_PAR3_DATE;
6.查看状态：
我们发现，由于主键索引是本地分区索引，做分区TRUNCATE时不会造成索引失效。
经过测试，我们发现对主键作为分区普通索引和分区唯一索引时的表现特征一致。直接分区并改造为分区表时，为了高效的迁移父表数据，需要对约束做一系列启停的操作，且子表的维护需要单独删除或转移数据。但由于是本地索引不会造成索引的失效问题。针对主键分区唯一索引的情况，维护的成本主要来自于子表数据的维护。
第三章 主键之外的列做为分区键列
上述讨论的主要是对主键列直接分区，而在实际的应用环境中，对主键直接分区还是少数情况。大部分还是对日期时间做范围分区或列表分区，以下作为重点测试。
主键作为全局唯一索引
建表语句如下：
--创建父表
create table test_par1
(id number, --主键列
tdate DATE )
partition by range ( tdate )
( partition p1 values less than (TO_DATE('20200201','YYYYMMDD')),
partition p2 values less than (TO_DATE('20200301','YYYYMMDD')),
partition pm values less than (MAXVALUE)) ;
ALTER TABLE test_par1 ADD CONSTRAINT IDX_PAR1_ID PRIMARY KEY(ID);
--创建子表
create table test_par1_FKT
(ID number,
tdate DATE );
--定义外键
ALTER TABLE test_par1_FKT ADD CONSTRAINT FK_PAR1_DATE FOREIGN KEY(id ) REFERENCES test_par1(id) on delete set null;
插入测试数据：
insert ALL
into test_par1
into test_par1_FKT
select 1,sysdate from dual
union select 2,sysdate-10 from dual
union select 3,sysdate -40 from dual;
commit;
查看数据；
SELECT t1.id id1,t1.tdate t1date,t2.id id2,t2.tdate t2date FROM test_par1 t1,test_par1_FKT t2 where t1.tdate=t2.tdate;
select table_name,partition_name,num_rows from dba_tab_partitions where table_name like 'TEST_PAR1';
下面做分区消除的测试：
1.删除数据：
TRUNCATE分区
DELETE父表数据：
DELETE FROM test_par1 where id=1;
由于外键定义使用了ON DELETE SET NULL参数，删除父表记录时子表对应记录自动设为NULL值。但仍然无法TRUNCATE分区。
为了利用“高效的分区消除”特性。需要按照如下步骤来分别实现：
2.禁用外键约束：
alter table test_par1_FKT disable constraint FK_PAR1_DATE;
3.删除子表数据：
delete from test_par1_FKT
where exists(select 1 from test_par1 partition(p2) t1 where t1.id=test_par1_FKT.id
and t1.tdate=xxx /* 期望清理的主表分区条件 */);
commit; （注：如果与删除父表分区是不同的会话，需要提交。所以，这里提交，我认为更安全和稳妥。);
4.父表做分区删除：
alter table test_par1 truncate partition p2;
5.启用子表外键约束：
alter table test_par1_FKT enable constraint FK_PAR1_DATE;
6.查看状态：
我们发现，由于主键索引是全局索引，做分区TRUNCATE时导致了主键索引的失效，因此还需要在分区TRUNCATE时添加 update global indexes参数或重建索引：
alter index IDX_PAR1_ID rebuild;
经过测试，我们发现对父表主键外的列进行分区并改造为分区表时，为了高效的迁移父表数据，需要对约束做一系列启停的操作，且子表的维护需要单独删除或转移数据。另外父表的主键索引会失效，因此也需要重建主键索引。针对主键全局索引时维护的成本过大。
主键作为本地分区索引
为了将主键建为本地索引，需要将分区列加入到主键列中，作为主键的本地分区索引。特别说明：这将可能导致原主键列出现了重复的值，但与分区键列组合后，并不重复的情况。举例来说，假设原ID列为主键列，即ID列中不会出现重复值。但因为分区改造，所以，要求分区表的主键列中必须包含分区键列。这就可能导致，即使ID列中出现了重复值，只要它与分区键列组合后的值不重复也是可以的。如果业务或应用逻辑上，不能接受ID列出现重复的情况，同时，技术手段上也不能保证ID列的数值不重复，那么，这种将主键列变为本地分区索引的方案就是行不通的。
建表语句如下：
--创建父表
create table test_par2
(id number,
tdate DATE )
partition by range ( tdate )
( partition p1 values less than (TO_DATE('20200201','YYYYMMDD')),
partition p2 values less than (TO_DATE('20200301','YYYYMMDD')),
partition pm values less than (MAXVALUE)) ;
--复合主键索引
create index IDX_PAR2_ID on test_par2(ID,tdate) local;
alter table test_par2 add constraint IDX_PAR2_ID primary key(ID,tdate);
--创建子表
create table test_par2_FKT
(ID number,
tdate DATE );
插入测试数据：
insert ALL
into test_par2
into test_par2_FKT
select 1,sysdate from dual
union select 2,sysdate-10 from dual
union select 3,sysdate -40 from dual;
commit;
查看数据；
SELECT t1.id id1,t1.tdate t1date,t2.id id2,t2.tdate t2date FROM test_par2 t1,test_par2_FKT t2 where t1.tdate=t2.tdate;
select table_name,partition_name,num_rows from dba_tab_partitions where table_name like 'TEST_PAR2';
测试步骤：
1.首先测试能否只对子表的ID列（原计划的外键列）与父表的复合主键建立外键约束。
ALTER TABLE test_par2_FKT ADD CONSTRAINT FK_PAR2_DATE FOREIGN KEY(id ) REFERENCES test_par2(id) on delete set null;
由于父表的主键已经增加了分区键tdate列构成复合主键索引，因此子表的外键只用对应的ID列是无法与父表主键匹配的。
2.增加子表的TDATE列测试：
ALTER TABLE test_par2_FKT ADD CONSTRAINT FK_PAR2_DATE FOREIGN KEY(id,tdate ) REFERENCES test_par2(id,tdate) on delete set null;
只能采用子表的ID+TDATE列与主表的ID+分区列TDATE一同构成主外键约束。此处请注意,子表的tdate列数据还必须来源于主表的tdate列，否则将导致时间差异等不匹配。
下面做分区消除的测试：
1.删除数据：
TRUNCATE分区
DELETE父表数据：
DELETE FROM test_par2 where id=1;
由于外键定义使用了ON DELETE SET NULL参数，删除父表记录时子表对应记录自动设为NULL值；
为了利用“高效的分区消除”特性。需要按照如下步骤来分别实现：
2.禁用外键约束：
alter table test_par2_FKT disable constraint FK_PAR2_DATE;
3.删除子表数据：
delete from test_par2_FKT
where exists(select 1 from test_par2 partition(p2) t1 where t1.id=test_par2_FKT.id
and t1.tdate=xxx /* 期望清理的主表分区条件 */);
commit; （注：如果与删除父表分区是不同的会话，需要提交。所以，这里提交，我认为更安全和稳妥。
4.父表做分区删除：
alter table test_par2 truncate partition p2;
5.启用子表外键约束：
alter table test_par2_FKT enable constraint FK_PAR2_DATE;
6.查看状态：
我们发现，由于主键索引是分区本地索引，做分区TRUNCATE时并不会导致索引失效。
经过测试，我们发现对父表主键外的列分区并改造为分区表时，为了高效的迁移父表数据，需要对约束做一系列启停的操作，且子表的维护需要单独删除或转移数据。另外由于父表的主键包含分区列且为本地索引，分区删除并不会导致索引失效。但子表的插入操作需要同步从父表获取到分区键列内容同步插入。
针对主键的分区本地索引，维护的成本主要来自于子表数据的维护。
将外键表改造为引用分区
基于以上的不同场景测试，我们发现：
不论对主键索引采用何种创建方法，对应的外键子表中的数据维护均不可避免。进而也就造成了不低的维护成本及停机时间。为了解决上述问题。我们可以引入一个新的分区类型 ：引用分区。
引用分区表是Oracle11g引入的新特性，解决了“并非所有子表都具有父表的分区列”这个问题。采用引用分区，子表会继承父表的分区机制。是通过PARTITION BY REFERENCE语句来实现，需要指定引用约束的名称，该约束将成为应用到该引用分区表所使用的约束。这个约束必须启用和执行。
引用分区可以实现父表的分区删除时，子表自动实现同步删除的动作。子表无需具有父表对应的分区列。子表的分区与父表分区保持一致。如果要指定分区描述，则分区描述的个数必须和父表的分区数保持一致。如果父表是一个复合分区表，则子表的分区和父表的子分区（注：是第二级分区）一一对应；否则，子表分区和父表的分区一一对应。
建表语句如下：
--创建父表
create table test_par3
(id number,
tdate DATE )
partition by range ( tdate )
( partition p1 values less than (TO_DATE('20200201','YYYYMMDD')),
partition p2 values less than (TO_DATE('20200301','YYYYMMDD')),
partition pm values less than (MAXVALUE)) ;
--主键全局索引
alter table test_par3 add constraint IDX_PAR3_ID primary key(ID);
--创建引用分区子表
create table test_par3_FKT
(ID number not null, --外键列必须为NOT NULL
CONSTRAINT FK_PAR3_DATE FOREIGN KEY(ID) REFERENCES test_par3(ID))
PARTITION BY REFERENCE (FK_PAR3_DATE);
插入测试数据：
insert into test_par3
select 1,sysdate from dual
union select 2,sysdate-10 from dual
union select 3,sysdate -40 from dual
union select 4,sysdate from dual;
insert into test_par3_FKT
select 1 from dual
union select 2 from dual
union select 3 from dual
union select 4 from dual;
commit;
查看数据；
SELECT t1.id id1,t1.tdate t1date,t2.id id2 FROM test_par3 t1,test_par3_FKT t2 where t1.id(+)=t2.id;
select table_name,partition_name,num_rows from dba_tab_partitions where table_name like 'TEST_PAR3';
可以看到，在不指定引用分区名称的情况下，分区名称与父表分区保持一致。且子表的分区数据也根据主外键约束关系自动存储相应的分区。
测试步骤：
1.DELETE父表数据：
DELETE FROM test_par3 where id=1;
由于外键定义没有使用ON DELETE参数，因此在引用分区中存在记录时不允许删除父表记录时。
将引用分区调整的外键定义调整为ON DELETE CASCADE；
create table test_par3_FKT
(ID number not null,
CONSTRAINT FK_PAR3_DATE FOREIGN KEY(ID)
REFERENCES test_par3(ID) on delete cascade)
PARTITION BY REFERENCE (FK_PAR3_DATE);
可以看到，当引用分区中的外键定义调整为级联删除后，对父表记录的删除会同步删除引用分区中的数据。
将引用分区调整的外键定义调整为ON DELETE SET NULL；
调整为SET NULL参数后提示不支持此种约束。
试想一下，如果对引用分区表的ID列设为NULL后，则缺少了可以做分区划分的列，因此数据库不知道将对应记录存在哪个分区上了。不支持该参数也是正常。
2.测试TRUNCATE父表分区：
可以看到，引用分区由于也是通过外键约束与父表关联的，因此其特性也与存在外键约束的普通表保持一致，不允许TRUNCATE父表。
3.测试DROP父表分区：
alter table test_par3 drop partition pm;
可以看到，DROP父表的分区，子表中的分区也会同步删除。
查看父表主键索引状态：
由于对父表采取了DROP分区的操作，导致了全局主键索引的失效，因此对于全局索引，在删除主键分区时还需要加上update global indexes参数或重建索引。
我们发现，已经失效的索引再次通过update global indexes参数的方式是无法重建的。
必须重建索引后在下次DDL时添加该参数，才能保证全局索引不再失效。
4.测试增加父表分区：
alter table test_par3 add partition po values less than (TO_DATE('20200315','YYYYMMDD'));
可以看到，父表增加了分区数据，子表也会同步创建对应分区，且父表全局索引不会受影响。
5.测试父表主键改为本地索引：
为了降低重建主键全局索引的成本，将父表的主键定义为本地索引。
create table test_par3
(ID number not null ,
tdate DATE
)
partition by range ( tdate )
( partition p1 values less than (TO_DATE('20200201','YYYYMMDD')),
partition p2 values less than (TO_DATE('20200301','YYYYMMDD')),
partition pm values less than (MAXVALUE)) ;
create index idx_part3_tdate on test_par3(id,tdate) local;
alter table test_par3 add constraint pk_par3_tdate primary key(id,tdate);
create table test_par3_FKT
(ID number not null,
tdate DATE not null,
CONSTRAINT FK_PAR3_DATE FOREIGN KEY(ID,tdate) REFERENCES test_par3(ID,tdate) on delete cascade)
PARTITION BY REFERENCE (FK_PAR3_DATE);
--插入数据
insert all
into test_par3
into test_par3_FKT
select 1,sysdate from dual
union select 2,sysdate-10 from dual
union select 3,sysdate -40 from dual
union select 4,sysdate-10 from dual;
commit;
父表分区列TDATE，主键列是ID+分区列TDATE，子表是根据外键约束的引用分区。
可以看到，删除父表分区后，父表的主键索引依然有效。
6.分析引用分区的结构：
可以看到，引用分区是通过外键引用的列作为分区键列。其与父表的分区结构及数据保持一致。即子表的分区数据均引用于父表的相关分区中的对应主键列数据。
因此推断对引用分区的分区扫描也是按照外键列来实现的：
可以看到，引用分区确实是按照引用的外键列来做分区条件的。
经过测试，我们发现对创建了引用分区的父表执行DML操作与传统的普通表特性一致，均要满足主外键约束。除此之外，还具有了传统分区表不具有的DDL特性，可以对父表做分区的删除增加等操作。子表会同步调整相应的分区数据。但由于父表的TRUNCATE是截断所有数据，由于主外键的关系还不能执行。
利用此种特性，可以高效的实现父表及子表的分区数据删除操作。但需要将原有的普通表均改为分区表的结构。改动成本较大。
第四章 总结
基于以上分析及测试，对于待改造为分区表的包含主外键的表，如果可以将父表改造为分区表的同时，将子表同步改为引用分区表。则可以有效降低表的维护成本。高效的查询及删除父表及子表数据。
但如果子表不允许改造为分区表，则只能在维护父表时，通过DELETE删除数据。或者采用禁用外键约束，来TRUNCATE父表指定分区的数据，但与此同时就造成了此段时间子表的数据约束很难保证。
当然，如果主外键约束能在应用层面控制，而无需数据库通过主外键的方式来保证，那以上问题也都不存在了。
