由于业务需求, 说历史表查询慢，所以决定把一个普通表转成分区表, 按照时间来分区, 我这里是按月分区,
思路如下:
1.先创建一个新的分区表,
create table TB_BA_AA_NEW
(
seq_id NUMBER(12) not null,
seq_nbr NUMBER(4) not null,
subs_id NUMBER(12) not null,
msinfo_id NUMBER(10) not null,
hist_create_date DATE,
msparam_id NUMBER(10)
)
rowdependencies
partition by range (HIST_CREATE_DATE)
(
partition P201512 values less than (TO_DATE(' 2016-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
partition P201601 values less than (TO_DATE(' 2016-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
);
2. 然后按每个月建成一个表
create table TB_BA_AA_1512
(
seq_id NUMBER(12) not null,
seq_nbr NUMBER(4) not null,
subs_id NUMBER(12) not null,
msinfo_id NUMBER(10) not null,
hist_create_date DATE,
msparam_id NUMBER(10)
)
;
create table TB_BA_AA_1601
(
seq_id NUMBER(12) not null,
seq_nbr NUMBER(4) not null,
subs_id NUMBER(12) not null,
msinfo_id NUMBER(10) not null,
hist_create_date DATE,
msparam_id NUMBER(10)
)
;
3.导入数据:
insert into TB_BA_AA_1512
SELECT /*+ append nologging */ /*+PARALLEL(a,5)*/ *
FROM TB_BA_AA
WHERE hist_create_date between TO_DATE('2015-12-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN') and TO_DATE('2015-12-31 23:59:59', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN');
commit;
insert into TB_BA_AA_1601
SELECT /*+ append nologging */ /*+PARALLEL(a,5)*/ *
FROM TB_BA_AA
WHERE hist_create_date between TO_DATE('2016-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN') and TO_DATE('2016-01-31 23:59:59', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN');
commit;
4.分区交换
alter table TB_BA_AA_NEW exchange partition P201601 with table TB_BA_AA_1601;
但是执行上面的语句时报错 : oralce ORA-14132: table cannot be used in EXCHANGE
查询找了外国的英文网站提供如下的信息,但是并没有我想要的
ORA-14132: table cannot be used in EXCHANGE
Cause: An attempt was made to issue an ALTER TABLE EXCHANGE PARTITION | SUBPARTITION command, but the non-partitioned table cannot be used in the EXCHANGE because one or more of the following apply:
– it is a typed table
– it is a temporary table
– it contains ADT columns
– it contains nested-table columns
– it contains REF columns
– it contains array columns
– it is an index-organized table
– it contains LOB columns
– it is a nested table --嵌套表
– it is created with row dependency and the partitioned table is not
– it is created without row dependency and the partitioned table is
Action :
Make sure the non-partitioned table does not violate any of the above restrictions for the ALTER TABLE EXCHANGE PARTITION | SUBPARTITION command.
看回去之前的建表语句发现 create table TB_BA_AA_NEW 加入了rowdependencies 行追踪, 而TB_BA_AA_1601并没有加,导致报错.
问题解决
网友评论