需求描述:
一个表已经上线,但是上线之后发现该表的数据量很大,而且会删除历史数据,该表上要建立多个唯一索引,现在的问题就是在建表的时候是建立了一个普通表,现在需要将普通表转化为分区表,并尽量减少对应用的影响
1、使用ctas的方式来建立
create table t1
(
ID VARCHAR2(60) not null,
a VARCHAR2(60) not null,
b VARCHAR2(60) not null,
create_time TIMESTAMP(6) not null,
d NUMBER(2) not null,
e VARCHAR2(400) not null,
OUTER_ID VARCHAR2(100)
)----原表
---建立分区表:
create table t1_new
(
ID_1 ,
a ,
b ,
create_time ,
d ,
e ,
OUTER_ID
)partition by range (ID_1)
(
partition p_t1_new values less than ('201204'),
partition p_t2_new values less than ('201205'))
as
select * from t1;----此时应用还是不断的往t1表中插入数据-
alter table t1_new add constraints pk_t1_new primary key (id) using tablespace local index_tablespace;----建立本地索引为了删除历史数据
/alter table t1_new add constraints pk_t1_new primary key (id_1) using index local ;
但是应用还在outer_id上建立唯一索引
create unique index IDX_t1_new_2 on t1_new (OUTER_ID) LOCAL ;
此时报
ORA-14039: 分区列必须构成 UNIQUE 索引的关键字列子集
原来oracle不支持在分区表上创建PK主键时主键列不包含分区列,创建另外的约束(unique)也不可以。
为了解决这个问题,两种方法
1、将分区键加到唯一索引里面
2、将local索引变为global索引----此时当进行历史数据删除的时候会导致索引失效
权衡之后在outer_id上建立普通索引
alter table t1 rename to t1_bak;
alter table t1_new rename to t1;
在进行ctas之后需要进行数据完整新检查,而且在进行rename的时候会导致应用连接失效(也有可能在进行alter的时候获取不到表锁而失败)---适用于修改不很频繁的表
2、使用交换分区的方式
----原表
create table t1
(
ID VARCHAR2(60) not null,
a VARCHAR2(60) not null,
b VARCHAR2(60) not null,
create_time TIMESTAMP(6) not null,
d NUMBER(2) not null,
e VARCHAR2(400) not null,
OUTER_ID VARCHAR2(100)
)
alter table t1 add constraints pk_t1 primary key(id) ;
新表
create table t1_new
(
ID_1 ,
a ,
b ,
create_time ,
d ,
e ,
OUTER_ID
)partition by range (ID_1)
(
partition p_t1_new values less than ('201204'),
partition p_t2_new values less than ('201205'))
alter table t1_new add constraints pk_t1_new primary key (id_1) using index local ;
ALTER TABLE t1_new EXCHANGE PARTITION p_t1_new WITH TABLE t1 without validation;----要不要加上without validation(加上的话就是不验证数据是否有效,直接全部放在p_t1_new分区中)
然后进行rename操作
优点:只是对数据字典中分区和表的定义进行了修改,没有数据的修改或复制,效率最高。如果对数据在分区中的分布没有进一步要求的话,实现比较简单。在执行完RENAME操作后,可以检查T_OLD中是否存在数据,如果存在的话,直接将这些数据插入到T中,可以保证对T插入的操作不会丢失。
不足:仍然存在一致性问题,交换分区之后RENAME T_NEW TO T之前,查询、更新和删除会出现错误或访问不到数据。如果要求数据分布到多个分区中,则需要进行分区的SPLIT操作,会增加操作的复杂度,效率也会降低。
适用于包含大数据量的表转到分区表中的一个分区的操作。应尽量在闲时进行操作。
3、在线重定义
----原表
create table t1
(
ID VARCHAR2(60) not null,
a VARCHAR2(60) not null,
b VARCHAR2(60) not null,
create_time TIMESTAMP(6) not null,
d NUMBER(2) not null,
e VARCHAR2(400) not null,
OUTER_ID VARCHAR2(100)
)
alter table t1 add constraints pk_t1 primary key(id) ;
新表
create table t1_new
(
ID_1 ,
a ,
b ,
create_time ,
d ,
e ,
OUTER_ID
)partition by range (ID_1)
(
partition p_t1_new values less than ('201204'),
partition p_t2_new values less than ('201205'))
alter table t1_new add constraints pk_t1_new primary key (id_1) using index local ;
一个表已经上线,但是上线之后发现该表的数据量很大,而且会删除历史数据,该表上要建立多个唯一索引,现在的问题就是在建表的时候是建立了一个普通表,现在需要将普通表转化为分区表,并尽量减少对应用的影响
1、使用ctas的方式来建立
create table t1
(
ID VARCHAR2(60) not null,
a VARCHAR2(60) not null,
b VARCHAR2(60) not null,
create_time TIMESTAMP(6) not null,
d NUMBER(2) not null,
e VARCHAR2(400) not null,
OUTER_ID VARCHAR2(100)
)----原表
---建立分区表:
create table t1_new
(
ID_1 ,
a ,
b ,
create_time ,
d ,
e ,
OUTER_ID
)partition by range (ID_1)
(
partition p_t1_new values less than ('201204'),
partition p_t2_new values less than ('201205'))
as
select * from t1;----此时应用还是不断的往t1表中插入数据-
alter table t1_new add constraints pk_t1_new primary key (id) using tablespace local index_tablespace;----建立本地索引为了删除历史数据
/alter table t1_new add constraints pk_t1_new primary key (id_1) using index local ;
但是应用还在outer_id上建立唯一索引
create unique index IDX_t1_new_2 on t1_new (OUTER_ID) LOCAL ;
此时报
ORA-14039: 分区列必须构成 UNIQUE 索引的关键字列子集
原来oracle不支持在分区表上创建PK主键时主键列不包含分区列,创建另外的约束(unique)也不可以。
为了解决这个问题,两种方法
1、将分区键加到唯一索引里面
2、将local索引变为global索引----此时当进行历史数据删除的时候会导致索引失效
权衡之后在outer_id上建立普通索引
alter table t1 rename to t1_bak;
alter table t1_new rename to t1;
在进行ctas之后需要进行数据完整新检查,而且在进行rename的时候会导致应用连接失效(也有可能在进行alter的时候获取不到表锁而失败)---适用于修改不很频繁的表
2、使用交换分区的方式
----原表
create table t1
(
ID VARCHAR2(60) not null,
a VARCHAR2(60) not null,
b VARCHAR2(60) not null,
create_time TIMESTAMP(6) not null,
d NUMBER(2) not null,
e VARCHAR2(400) not null,
OUTER_ID VARCHAR2(100)
)
alter table t1 add constraints pk_t1 primary key(id) ;
新表
create table t1_new
(
ID_1 ,
a ,
b ,
create_time ,
d ,
e ,
OUTER_ID
)partition by range (ID_1)
(
partition p_t1_new values less than ('201204'),
partition p_t2_new values less than ('201205'))
alter table t1_new add constraints pk_t1_new primary key (id_1) using index local ;
ALTER TABLE t1_new EXCHANGE PARTITION p_t1_new WITH TABLE t1 without validation;----要不要加上without validation(加上的话就是不验证数据是否有效,直接全部放在p_t1_new分区中)
然后进行rename操作
优点:只是对数据字典中分区和表的定义进行了修改,没有数据的修改或复制,效率最高。如果对数据在分区中的分布没有进一步要求的话,实现比较简单。在执行完RENAME操作后,可以检查T_OLD中是否存在数据,如果存在的话,直接将这些数据插入到T中,可以保证对T插入的操作不会丢失。
不足:仍然存在一致性问题,交换分区之后RENAME T_NEW TO T之前,查询、更新和删除会出现错误或访问不到数据。如果要求数据分布到多个分区中,则需要进行分区的SPLIT操作,会增加操作的复杂度,效率也会降低。
适用于包含大数据量的表转到分区表中的一个分区的操作。应尽量在闲时进行操作。
3、在线重定义
----原表
create table t1
(
ID VARCHAR2(60) not null,
a VARCHAR2(60) not null,
b VARCHAR2(60) not null,
create_time TIMESTAMP(6) not null,
d NUMBER(2) not null,
e VARCHAR2(400) not null,
OUTER_ID VARCHAR2(100)
)
alter table t1 add constraints pk_t1 primary key(id) ;
新表
create table t1_new
(
ID_1 ,
a ,
b ,
create_time ,
d ,
e ,
OUTER_ID
)partition by range (ID_1)
(
partition p_t1_new values less than ('201204'),
partition p_t2_new values less than ('201205'))
alter table t1_new add constraints pk_t1_new primary key (id_1) using index local ;
EXEC DBMS_REDEFINITION.CAN_REDEF_TABLE(USER, 'T1', DBMS_REDEFINITION.CONS_USE_PK);
EXEC DBMS_REDEFINITION.START_REDEF_TABLE(USER, 'T1', 'T1_NEW',DBMS_REDEFINITION.CONS_USE_PK);