最近要进行数据的归档，原表是一个按照时间进行的分区表，历史表是按照原表结构创建的。
原表只保留半年的数据。打算采用EXCHANGE PARTITION WITH TABLE的方式进行数据归档。
由于EXCHANGE PARTITION WITH TABLE的方式不支持分区表与分区表的交换。因此需要首先创建一个和原表
表结构一模一样的非分区表。
原表MKT_EXEC_EFFECT表位于MKT_DATA表空间，历史表HIS_MKT_EXEC_EFFECT位于HIS_DATA表空间,
新创建的中间表MID_MKT_EXEC_EFFECT位于HIS_DATA表空间。
使用ALTER TABLE MKT_EXEC_EFFECT EXCHANGE PARTITION P1 WITH TABLE HIS_MKT_EXEC_EFFECT直接进行交换是不行的。
SQL> ALTER TABLE MKT_EXEC_EFFECT EXCHANGE PARTITION P1 WITH TABLE HIS_MKT_EXEC_EFFECT;
ALTER TABLE MKT_EXEC_EFFECT EXCHANGE PARTITION P1 WITH TABLE HIS_MKT_EXEC_EFFECT
*
ERROR at line 1:
ORA-14095: ALTER TABLE EXCHANGE requires a non-partitioned, non-clustered table
Elapsed: 00:00:00.25
SQL> ALTER TABLE MKT_EXEC_EFFECT EXCHANGE PARTITION P1 WITH TABLE HIS_MKT_EXEC_EFFECT PARTITION (P1);
ALTER TABLE MKT_EXEC_EFFECT EXCHANGE PARTITION P1 WITH TABLE HIS_MKT_EXEC_EFFECT PARTITION (P1)
*
ERROR at line 1:
ORA-14052: partition-extended table name syntax is disallowed in this context
Elapsed: 00:00:00.01
首先创建和原表、历史表表结构一模一样的中间表MID_MKT_EXEC_EFFECT。
SQL> CREATE TABLE MID_MKT_EXEC_EFFECT
2 TABLESPACE HIS_DATA
3 AS
4 SELECT * FROM MKT_EXEC_EFFECT
5 WHERE 1=0;
Table created.
Elapsed: 00:00:00.66
将原表数据MKT_EXEC_EFFECT的分区P1交换到中间表MID_MKT_EXEC_EFFECT中。
SQL> SELECT COUNT(1) FROM MKT_EXEC_EFFECT PARTITION (P1);
COUNT(1)
----------
339500
Elapsed: 00:00:00.09
SQL> ALTER TABLE MKT_EXEC_EFFECT EXCHANGE PARTITION P1 WITH TABLE MID_MKT_EXEC_EFFECT;
Table altered.
Elapsed: 00:00:48.11
SQL> SELECT COUNT(1) FROM MKT_EXEC_EFFECT PARTITION (P1);
COUNT(1)
----------
0
Elapsed: 00:00:00.01
SQL> SELECT COUNT(1) FROM MID_MKT_EXEC_EFFECT;
COUNT(1)
----------
339500
Elapsed: 00:00:00.84
可以看到数据已经正常交换到中间表里了,ORACLE在交换过程中直接修改的数据字典，
这将导致MKT_EXEC_EFFECT P1分区所在的表空间和 MID_MKT_EXEC_EFFECT 表所在的表空间 进行了交换。
SQL> SELECT TABLESPACE_NAME
2 FROM USER_TAB_PARTITIONS
3 WHERE TABLE_NAME='MKT_EXEC_EFFECT' AND PARTITION_NAME='P1';
TABLESPACE_NAME
------------------------------
HIS_DATA
SQL> SELECT TABLESPACE_NAME FROM USER_TABLES WHERE TABLE_NAME='MID_MKT_EXEC_EFFECT';
TABLESPACE_NAME
------------------------------
MKT_DATA
这点需要注意。
下一步将进行中间表到历史表的交换。
SQL> SELECT COUNT(1) FROM HIS_MKT_EXEC_EFFECT;
COUNT(1)
----------
0
Elapsed: 00:00:00.00
SQL> ALTER TABLE HIS_MKT_EXEC_EFFECT EXCHANGE PARTITION P1 WITH TABLE MID_MKT_EXEC_EFFECT;
Table altered.
Elapsed: 00:00:00.32
SQL> SELECT COUNT(1) FROM HIS_MKT_EXEC_EFFECT;
COUNT(1)
----------
339500
Elapsed: 00:00:00.03
SQL> SELECT COUNT(1) FROM MID_MKT_EXEC_EFFECT;
COUNT(1)
----------
0
Elapsed: 00:00:00.01
数据已经正常交换过来了。
依次交换类推交换其他分区P2，P3，.....
ALTER TABLE MKT_EXEC_EFFECT EXCHANGE PARTITION P2 WITH TABLE MID_MKT_EXEC_EFFECT;
ALTER TABLE HIS_MKT_EXEC_EFFECT EXCHANGE PARTITION P2 WITH TABLE MID_MKT_EXEC_EFFECT;
ALTER TABLE MKT_EXEC_EFFECT EXCHANGE PARTITION P3 WITH TABLE MID_MKT_EXEC_EFFECT;
ALTER TABLE HIS_MKT_EXEC_EFFECT EXCHANGE PARTITION P3 WITH TABLE MID_MKT_EXEC_EFFECT;
ALTER TABLE MKT_EXEC_EFFECT EXCHANGE PARTITION P4 WITH TABLE MID_MKT_EXEC_EFFECT;
ALTER TABLE HIS_MKT_EXEC_EFFECT EXCHANGE PARTITION P4 WITH TABLE MID_MKT_EXEC_EFFECT;
ALTER TABLE MKT_EXEC_EFFECT EXCHANGE PARTITION P5 WITH TABLE MID_MKT_EXEC_EFFECT;
ALTER TABLE HIS_MKT_EXEC_EFFECT EXCHANGE PARTITION P5 WITH TABLE MID_MKT_EXEC_EFFECT;
ALTER TABLE MKT_EXEC_EFFECT EXCHANGE PARTITION P6 WITH TABLE MID_MKT_EXEC_EFFECT;
ALTER TABLE HIS_MKT_EXEC_EFFECT EXCHANGE PARTITION P6 WITH TABLE MID_MKT_EXEC_EFFECT;
ALTER TABLE MKT_EXEC_EFFECT EXCHANGE PARTITION P7 WITH TABLE MID_MKT_EXEC_EFFECT;
ALTER TABLE HIS_MKT_EXEC_EFFECT EXCHANGE PARTITION P7 WITH TABLE MID_MKT_EXEC_EFFECT;
ALTER TABLE MKT_EXEC_EFFECT EXCHANGE PARTITION P8 WITH TABLE MID_MKT_EXEC_EFFECT;
ALTER TABLE HIS_MKT_EXEC_EFFECT EXCHANGE PARTITION P8 WITH TABLE MID_MKT_EXEC_EFFECT;
ALTER TABLE MKT_EXEC_EFFECT EXCHANGE PARTITION P9 WITH TABLE MID_MKT_EXEC_EFFECT;
ALTER TABLE HIS_MKT_EXEC_EFFECT EXCHANGE PARTITION P9 WITH TABLE MID_MKT_EXEC_EFFECT;
ALTER TABLE MKT_EXEC_EFFECT EXCHANGE PARTITION P10 WITH TABLE MID_MKT_EXEC_EFFECT;
ALTER TABLE HIS_MKT_EXEC_EFFECT EXCHANGE PARTITION P10 WITH TABLE MID_MKT_EXEC_EFFECT;
ALTER TABLE MKT_EXEC_EFFECT EXCHANGE PARTITION P11 WITH TABLE MID_MKT_EXEC_EFFECT;
ALTER TABLE HIS_MKT_EXEC_EFFECT EXCHANGE PARTITION P11 WITH TABLE MID_MKT_EXEC_EFFECT;
ALTER TABLE MKT_EXEC_EFFECT EXCHANGE PARTITION P12 WITH TABLE MID_MKT_EXEC_EFFECT;
ALTER TABLE HIS_MKT_EXEC_EFFECT EXCHANGE PARTITION P12 WITH TABLE MID_MKT_EXEC_EFFECT;
ALTER TABLE MKT_EXEC_EFFECT EXCHANGE PARTITION P13 WITH TABLE MID_MKT_EXEC_EFFECT;
ALTER TABLE HIS_MKT_EXEC_EFFECT EXCHANGE PARTITION P13 WITH TABLE MID_MKT_EXEC_EFFECT;
ALTER TABLE MKT_EXEC_EFFECT EXCHANGE PARTITION P14 WITH TABLE MID_MKT_EXEC_EFFECT;
ALTER TABLE HIS_MKT_EXEC_EFFECT EXCHANGE PARTITION P14 WITH TABLE MID_MKT_EXEC_EFFECT;
ALTER TABLE MKT_EXEC_EFFECT EXCHANGE PARTITION P15 WITH TABLE MID_MKT_EXEC_EFFECT;
ALTER TABLE HIS_MKT_EXEC_EFFECT EXCHANGE PARTITION P15 WITH TABLE MID_MKT_EXEC_EFFECT;
ALTER TABLE MKT_EXEC_EFFECT EXCHANGE PARTITION P16 WITH TABLE MID_MKT_EXEC_EFFECT;
ALTER TABLE HIS_MKT_EXEC_EFFECT EXCHANGE PARTITION P16 WITH TABLE MID_MKT_EXEC_EFFECT;
ALTER TABLE MKT_EXEC_EFFECT EXCHANGE PARTITION P17 WITH TABLE MID_MKT_EXEC_EFFECT;
ALTER TABLE HIS_MKT_EXEC_EFFECT EXCHANGE PARTITION P17 WITH TABLE MID_MKT_EXEC_EFFECT;
ALTER TABLE MKT_EXEC_EFFECT EXCHANGE PARTITION P18 WITH TABLE MID_MKT_EXEC_EFFECT;
ALTER TABLE HIS_MKT_EXEC_EFFECT EXCHANGE PARTITION P18 WITH TABLE MID_MKT_EXEC_EFFECT;
ALTER TABLE MKT_EXEC_EFFECT EXCHANGE PARTITION P19 WITH TABLE MID_MKT_EXEC_EFFECT;
ALTER TABLE HIS_MKT_EXEC_EFFECT EXCHANGE PARTITION P19 WITH TABLE MID_MKT_EXEC_EFFECT;
ALTER TABLE MKT_EXEC_EFFECT EXCHANGE PARTITION P20 WITH TABLE MID_MKT_EXEC_EFFECT;
ALTER TABLE HIS_MKT_EXEC_EFFECT EXCHANGE PARTITION P20 WITH TABLE MID_MKT_EXEC_EFFECT;
ALTER TABLE MKT_EXEC_EFFECT EXCHANGE PARTITION P21 WITH TABLE MID_MKT_EXEC_EFFECT;
ALTER TABLE HIS_MKT_EXEC_EFFECT EXCHANGE PARTITION P21 WITH TABLE MID_MKT_EXEC_EFFECT;
ALTER TABLE MKT_EXEC_EFFECT EXCHANGE PARTITION P22 WITH TABLE MID_MKT_EXEC_EFFECT;
ALTER TABLE HIS_MKT_EXEC_EFFECT EXCHANGE PARTITION P22 WITH TABLE MID_MKT_EXEC_EFFECT;
ALTER TABLE MKT_EXEC_EFFECT EXCHANGE PARTITION P23 WITH TABLE MID_MKT_EXEC_EFFECT;
ALTER TABLE HIS_MKT_EXEC_EFFECT EXCHANGE PARTITION P23 WITH TABLE MID_MKT_EXEC_EFFECT;
ALTER TABLE MKT_EXEC_EFFECT EXCHANGE PARTITION P24 WITH TABLE MID_MKT_EXEC_EFFECT;
ALTER TABLE HIS_MKT_EXEC_EFFECT EXCHANGE PARTITION P24 WITH TABLE MID_MKT_EXEC_EFFECT;
ALTER TABLE MKT_EXEC_EFFECT EXCHANGE PARTITION P25 WITH TABLE MID_MKT_EXEC_EFFECT;
ALTER TABLE HIS_MKT_EXEC_EFFECT EXCHANGE PARTITION P25 WITH TABLE MID_MKT_EXEC_EFFECT;
ALTER TABLE MKT_EXEC_EFFECT EXCHANGE PARTITION P26 WITH TABLE MID_MKT_EXEC_EFFECT;
ALTER TABLE HIS_MKT_EXEC_EFFECT EXCHANGE PARTITION P26 WITH TABLE MID_MKT_EXEC_EFFECT;
ALTER TABLE MKT_EXEC_EFFECT EXCHANGE PARTITION P27 WITH TABLE MID_MKT_EXEC_EFFECT;
ALTER TABLE HIS_MKT_EXEC_EFFECT EXCHANGE PARTITION P27 WITH TABLE MID_MKT_EXEC_EFFECT;
ALTER TABLE MKT_EXEC_EFFECT EXCHANGE PARTITION P28 WITH TABLE MID_MKT_EXEC_EFFECT;
ALTER TABLE HIS_MKT_EXEC_EFFECT EXCHANGE PARTITION P28 WITH TABLE MID_MKT_EXEC_EFFECT;
ALTER TABLE MKT_EXEC_EFFECT EXCHANGE PARTITION P29 WITH TABLE MID_MKT_EXEC_EFFECT;
ALTER TABLE HIS_MKT_EXEC_EFFECT EXCHANGE PARTITION P29 WITH TABLE MID_MKT_EXEC_EFFECT;
ALTER TABLE MKT_EXEC_EFFECT EXCHANGE PARTITION P30 WITH TABLE MID_MKT_EXEC_EFFECT;
ALTER TABLE HIS_MKT_EXEC_EFFECT EXCHANGE PARTITION P30 WITH TABLE MID_MKT_EXEC_EFFECT;
ALTER TABLE MKT_EXEC_EFFECT EXCHANGE PARTITION P31 WITH TABLE MID_MKT_EXEC_EFFECT;
ALTER TABLE HIS_MKT_EXEC_EFFECT EXCHANGE PARTITION P31 WITH TABLE MID_MKT_EXEC_EFFECT;
ALTER TABLE MKT_EXEC_EFFECT EXCHANGE PARTITION P32 WITH TABLE MID_MKT_EXEC_EFFECT;
ALTER TABLE HIS_MKT_EXEC_EFFECT EXCHANGE PARTITION P32 WITH TABLE MID_MKT_EXEC_EFFECT;
ALTER TABLE MKT_EXEC_EFFECT EXCHANGE PARTITION P33 WITH TABLE MID_MKT_EXEC_EFFECT;
ALTER TABLE HIS_MKT_EXEC_EFFECT EXCHANGE PARTITION P33 WITH TABLE MID_MKT_EXEC_EFFECT;
ALTER TABLE MKT_EXEC_EFFECT EXCHANGE PARTITION P34 WITH TABLE MID_MKT_EXEC_EFFECT;
ALTER TABLE HIS_MKT_EXEC_EFFECT EXCHANGE PARTITION P34 WITH TABLE MID_MKT_EXEC_EFFECT;
ALTER TABLE MKT_EXEC_EFFECT EXCHANGE PARTITION P35 WITH TABLE MID_MKT_EXEC_EFFECT;
ALTER TABLE HIS_MKT_EXEC_EFFECT EXCHANGE PARTITION P35 WITH TABLE MID_MKT_EXEC_EFFECT;
ALTER TABLE MKT_EXEC_EFFECT EXCHANGE PARTITION P36 WITH TABLE MID_MKT_EXEC_EFFECT;
ALTER TABLE HIS_MKT_EXEC_EFFECT EXCHANGE PARTITION P36 WITH TABLE MID_MKT_EXEC_EFFECT;
ALTER TABLE MKT_EXEC_EFFECT EXCHANGE PARTITION P37 WITH TABLE MID_MKT_EXEC_EFFECT;
ALTER TABLE HIS_MKT_EXEC_EFFECT EXCHANGE PARTITION P37 WITH TABLE MID_MKT_EXEC_EFFECT;
ALTER TABLE MKT_EXEC_EFFECT EXCHANGE PARTITION P38 WITH TABLE MID_MKT_EXEC_EFFECT;
ALTER TABLE HIS_MKT_EXEC_EFFECT EXCHANGE PARTITION P38 WITH TABLE MID_MKT_EXEC_EFFECT;
ALTER TABLE MKT_EXEC_EFFECT EXCHANGE PARTITION P39 WITH TABLE MID_MKT_EXEC_EFFECT;
ALTER TABLE HIS_MKT_EXEC_EFFECT EXCHANGE PARTITION P39 WITH TABLE MID_MKT_EXEC_EFFECT;
ALTER TABLE MKT_EXEC_EFFECT EXCHANGE PARTITION P40 WITH TABLE MID_MKT_EXEC_EFFECT;
ALTER TABLE HIS_MKT_EXEC_EFFECT EXCHANGE PARTITION P40 WITH TABLE MID_MKT_EXEC_EFFECT;
ALTER TABLE MKT_EXEC_EFFECT EXCHANGE PARTITION P41 WITH TABLE MID_MKT_EXEC_EFFECT;
ALTER TABLE HIS_MKT_EXEC_EFFECT EXCHANGE PARTITION P41 WITH TABLE MID_MKT_EXEC_EFFECT;
ALTER TABLE MKT_EXEC_EFFECT EXCHANGE PARTITION P42 WITH TABLE MID_MKT_EXEC_EFFECT;
ALTER TABLE HIS_MKT_EXEC_EFFECT EXCHANGE PARTITION P42 WITH TABLE MID_MKT_EXEC_EFFECT;
ALTER TABLE MKT_EXEC_EFFECT EXCHANGE PARTITION P43 WITH TABLE MID_MKT_EXEC_EFFECT;
ALTER TABLE HIS_MKT_EXEC_EFFECT EXCHANGE PARTITION P43 WITH TABLE MID_MKT_EXEC_EFFECT;
ALTER TABLE MKT_EXEC_EFFECT EXCHANGE PARTITION P44 WITH TABLE MID_MKT_EXEC_EFFECT;
ALTER TABLE HIS_MKT_EXEC_EFFECT EXCHANGE PARTITION P44 WITH TABLE MID_MKT_EXEC_EFFECT;
ALTER TABLE MKT_EXEC_EFFECT EXCHANGE PARTITION P45 WITH TABLE MID_MKT_EXEC_EFFECT;
ALTER TABLE HIS_MKT_EXEC_EFFECT EXCHANGE PARTITION P45 WITH TABLE MID_MKT_EXEC_EFFECT;
ALTER TABLE MKT_EXEC_EFFECT EXCHANGE PARTITION P46 WITH TABLE MID_MKT_EXEC_EFFECT;
ALTER TABLE HIS_MKT_EXEC_EFFECT EXCHANGE PARTITION P46 WITH TABLE MID_MKT_EXEC_EFFECT;
ALTER TABLE MKT_EXEC_EFFECT EXCHANGE PARTITION P47 WITH TABLE MID_MKT_EXEC_EFFECT;
ALTER TABLE HIS_MKT_EXEC_EFFECT EXCHANGE PARTITION P47 WITH TABLE MID_MKT_EXEC_EFFECT;
ALTER TABLE MKT_EXEC_EFFECT EXCHANGE PARTITION P48 WITH TABLE MID_MKT_EXEC_EFFECT;
ALTER TABLE HIS_MKT_EXEC_EFFECT EXCHANGE PARTITION P48 WITH TABLE MID_MKT_EXEC_EFFECT;
ALTER TABLE MKT_EXEC_EFFECT EXCHANGE PARTITION P49 WITH TABLE MID_MKT_EXEC_EFFECT;
ALTER TABLE HIS_MKT_EXEC_EFFECT EXCHANGE PARTITION P49 WITH TABLE MID_MKT_EXEC_EFFECT;
ALTER TABLE MKT_EXEC_EFFECT EXCHANGE PARTITION P50 WITH TABLE MID_MKT_EXEC_EFFECT;
ALTER TABLE HIS_MKT_EXEC_EFFECT EXCHANGE PARTITION P50 WITH TABLE MID_MKT_EXEC_EFFECT;
ALTER TABLE MKT_EXEC_EFFECT EXCHANGE PARTITION P51 WITH TABLE MID_MKT_EXEC_EFFECT;
ALTER TABLE HIS_MKT_EXEC_EFFECT EXCHANGE PARTITION P51 WITH TABLE MID_MKT_EXEC_EFFECT;
ALTER TABLE MKT_EXEC_EFFECT EXCHANGE PARTITION P52 WITH TABLE MID_MKT_EXEC_EFFECT;
ALTER TABLE HIS_MKT_EXEC_EFFECT EXCHANGE PARTITION P52 WITH TABLE MID_MKT_EXEC_EFFECT;
ALTER TABLE MKT_EXEC_EFFECT EXCHANGE PARTITION P53 WITH TABLE MID_MKT_EXEC_EFFECT;
ALTER TABLE HIS_MKT_EXEC_EFFECT EXCHANGE PARTITION P53 WITH TABLE MID_MKT_EXEC_EFFECT;
ALTER TABLE MKT_EXEC_EFFECT EXCHANGE PARTITION P54 WITH TABLE MID_MKT_EXEC_EFFECT;
ALTER TABLE HIS_MKT_EXEC_EFFECT EXCHANGE PARTITION P54 WITH TABLE MID_MKT_EXEC_EFFECT;
ALTER TABLE MKT_EXEC_EFFECT EXCHANGE PARTITION P55 WITH TABLE MID_MKT_EXEC_EFFECT;
ALTER TABLE HIS_MKT_EXEC_EFFECT EXCHANGE PARTITION P55 WITH TABLE MID_MKT_EXEC_EFFECT;
ALTER TABLE MKT_EXEC_EFFECT EXCHANGE PARTITION P56 WITH TABLE MID_MKT_EXEC_EFFECT;
ALTER TABLE HIS_MKT_EXEC_EFFECT EXCHANGE PARTITION P56 WITH TABLE MID_MKT_EXEC_EFFECT;
ALTER TABLE MKT_EXEC_EFFECT EXCHANGE PARTITION P57 WITH TABLE MID_MKT_EXEC_EFFECT;
ALTER TABLE HIS_MKT_EXEC_EFFECT EXCHANGE PARTITION P57 WITH TABLE MID_MKT_EXEC_EFFECT;
ALTER TABLE MKT_EXEC_EFFECT EXCHANGE PARTITION P58 WITH TABLE MID_MKT_EXEC_EFFECT;
ALTER TABLE HIS_MKT_EXEC_EFFECT EXCHANGE PARTITION P58 WITH TABLE MID_MKT_EXEC_EFFECT;
ALTER TABLE MKT_EXEC_EFFECT EXCHANGE PARTITION P59 WITH TABLE MID_MKT_EXEC_EFFECT;
ALTER TABLE HIS_MKT_EXEC_EFFECT EXCHANGE PARTITION P59 WITH TABLE MID_MKT_EXEC_EFFECT;
ALTER TABLE MKT_EXEC_EFFECT EXCHANGE PARTITION P60 WITH TABLE MID_MKT_EXEC_EFFECT;
ALTER TABLE HIS_MKT_EXEC_EFFECT EXCHANGE PARTITION P60 WITH TABLE MID_MKT_EXEC_EFFECT;
ALTER TABLE MKT_EXEC_EFFECT EXCHANGE PARTITION P61 WITH TABLE MID_MKT_EXEC_EFFECT;
ALTER TABLE HIS_MKT_EXEC_EFFECT EXCHANGE PARTITION P61 WITH TABLE MID_MKT_EXEC_EFFECT;
ALTER TABLE MKT_EXEC_EFFECT EXCHANGE PARTITION P62 WITH TABLE MID_MKT_EXEC_EFFECT;
ALTER TABLE HIS_MKT_EXEC_EFFECT EXCHANGE PARTITION P62 WITH TABLE MID_MKT_EXEC_EFFECT;
ALTER TABLE MKT_EXEC_EFFECT EXCHANGE PARTITION P63 WITH TABLE MID_MKT_EXEC_EFFECT;
ALTER TABLE HIS_MKT_EXEC_EFFECT EXCHANGE PARTITION P63 WITH TABLE MID_MKT_EXEC_EFFECT;
ALTER TABLE MKT_EXEC_EFFECT EXCHANGE PARTITION P64 WITH TABLE MID_MKT_EXEC_EFFECT;
ALTER TABLE HIS_MKT_EXEC_EFFECT EXCHANGE PARTITION P64 WITH TABLE MID_MKT_EXEC_EFFECT;
ALTER TABLE MKT_EXEC_EFFECT EXCHANGE PARTITION P65 WITH TABLE MID_MKT_EXEC_EFFECT;
ALTER TABLE HIS_MKT_EXEC_EFFECT EXCHANGE PARTITION P65 WITH TABLE MID_MKT_EXEC_EFFECT;
ALTER TABLE MKT_EXEC_EFFECT EXCHANGE PARTITION P66 WITH TABLE MID_MKT_EXEC_EFFECT;
ALTER TABLE HIS_MKT_EXEC_EFFECT EXCHANGE PARTITION P66 WITH TABLE MID_MKT_EXEC_EFFECT;
ALTER TABLE MKT_EXEC_EFFECT EXCHANGE PARTITION P67 WITH TABLE MID_MKT_EXEC_EFFECT;
ALTER TABLE HIS_MKT_EXEC_EFFECT EXCHANGE PARTITION P67 WITH TABLE MID_MKT_EXEC_EFFECT;
ALTER TABLE MKT_EXEC_EFFECT EXCHANGE PARTITION P68 WITH TABLE MID_MKT_EXEC_EFFECT;
ALTER TABLE HIS_MKT_EXEC_EFFECT EXCHANGE PARTITION P68 WITH TABLE MID_MKT_EXEC_EFFECT;
ALTER TABLE MKT_EXEC_EFFECT EXCHANGE PARTITION P69 WITH TABLE MID_MKT_EXEC_EFFECT;
ALTER TABLE HIS_MKT_EXEC_EFFECT EXCHANGE PARTITION P69 WITH TABLE MID_MKT_EXEC_EFFECT;
ALTER TABLE MKT_EXEC_EFFECT EXCHANGE PARTITION P70 WITH TABLE MID_MKT_EXEC_EFFECT;
ALTER TABLE HIS_MKT_EXEC_EFFECT EXCHANGE PARTITION P70 WITH TABLE MID_MKT_EXEC_EFFECT;
168243699的数据归档用了不到10分钟。
利用EXCHANGE PARTITION WITH TABLE的方式进行数据归档能大大提高归档的效率，普通的INSERT,DELETE
归档方法在大数据量的情况下并不太适合。
EXCHANGE PARTITION WITH TABLE方法会导致全局索引失效，在归档完毕还需要进行索引的重建。
阅读(2229) | 评论(0) | 转发(0) |
