由于業(yè)務(wù)需求, 說歷史表查詢慢瞒滴，所以決定把一個(gè)普通表轉(zhuǎn)成分區(qū)表, 按照時(shí)間來分區(qū), 我這里是按月分區(qū),
思路如下:
1.先創(chuàng)建一個(gè)新的分區(qū)表,
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. 然后按每個(gè)月建成一個(gè)表
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.導(dǎo)入數(shù)據(jù):
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.分區(qū)交換
alter table TB_BA_AA_NEW exchange partition P201601 with table TB_BA_AA_1601;
但是執(zhí)行上面的語句時(shí)報(bào)錯(cuò) : oralce ORA-14132: table cannot be used in EXCHANGE
查詢找了外國(guó)的英文網(wǎng)站提供如下的信息,但是并沒有我想要的
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.
看回去之前的建表語句發(fā)現(xiàn) create table TB_BA_AA_NEW 加入了rowdependencies 行追蹤, 而TB_BA_AA_1601并沒有加,導(dǎo)致報(bào)錯(cuò).?
問題解決
