Oracle中使用分区表来归档冷数据

为了保持查询的效率我们应当将不用的数据放在归档表中

另一种场景是业务只需要当天的数据但是可能需要在后台查询历史数据以供错误分析等用途

这里演示一下如何使用Oracle的分区交换特性

来将数据归档到分区表

演示时主表是普通表只存一天数据每天跑批前将前一天的数据转移到按日分区的归档表

主表是分区表的情况可以自行推演

--使用分区表按日归档数据
--建立正常表 和索引
CREATE TABLE CUST_INFO
(
    CUST_ID VARCHAR2(30),
    CUST_NAME VARCHAR2(100),
    ETL_LOAD_DT DATE
)
NOLOGGING COMPRESS;

CREATE INDEX CUST_INFO_IDX ON CUST_INFO (CUST_ID);

--建立归档用的分区表 和分区索引 计划每天一个分区
--分区索引只为演示更加全面，一般归档表无需索引 
CREATE TABLE CUST_INFO_ARC
(
    CUST_ID VARCHAR2(30),
    CUST_NAME VARCHAR2(100),
    ETL_LOAD_DT DATE
)PARTITION BY RANGE(ETL_LOAD_DT)
(PARTITION CUST_INFO_ARC_INIT 
    VALUES LESS THAN(TO_DATE('20140201','YYYYMMDD'))
)
NOLOGGING COMPRESS;

CREATE INDEX CUST_INFO_ARC_IDX 
    ON CUST_INFO_ARC (CUST_ID) LOCAL;

--在正常表中插入数据 我们要把它归档到分区表中
--所以用作分区的ETL_LOAD_DT只能出现同一天的日期
INSERT INTO CUST_INFO VALUES('1','Tom',TO_DATE('20140202','YYYYMMDD'));
COMMIT;

--检查数据是否插入 结果为1
SELECT COUNT(*) FROM CUST_INFO;

--我们看看分区表的分区 结果只有一个字段 
--在HIGH_VALUE字段可以看到分区的区间定义
SELECT * FROM USER_TAB_PARTITIONS 
    WHERE TABLE_NAME='CUST_INFO_ARC';

--现在我们为了将数据归档，先要为当前日期建立一个分区 
--注意我们放2月2日的数据 分区定义为小于2月3日
ALTER TABLE CUST_INFO_ARC 
    ADD PARTITION CUST_INFO_ARC_20140202 
        VALUES LESS THAN(TO_DATE('20140203','YYYYMMDD'));

--再次查看分区状态，现在有两个分区了
SELECT * FROM USER_TAB_PARTITIONS 
    WHERE TABLE_NAME='CUST_INFO_ARC';

--在做操作前，我们检查两个索引的状态
--它们的状态应该都是VALID 或者USABLE
SELECT T.STATUS,T.* FROM USER_INDEXES T 
    WHERE INDEX_NAME='CUST_INFO_IDX';
SELECT T.STATUS,T.* FROM USER_IND_PARTITIONS T 
    WHERE T.INDEX_NAME='CUST_INFO_ARC_IDX';

--重要：我们现在利用分区交换特性，将正常表中的数据交换到新建的分区
--注意日期必须与分区的限定匹配，否则会报错，有兴趣的可以自行尝试
ALTER TABLE CUST_INFO_ARC 
    EXCHANGE PARTITION CUST_INFO_ARC_20140202 
        WITH TABLE CUST_INFO;

--检查一下记录是不是已经被交换了 记录应该在分区表里
SELECT COUNT(*) FROM CUST_INFO;
SELECT COUNT(*) FROM CUST_INFO_ARC;

--现在我们再次检查两个索引的状态，交换操作并不会维护索引
--所以正常表的索引和被交换的那个分区索引都会失效。
SELECT T.STATUS,T.* FROM USER_INDEXES T 
    WHERE INDEX_NAME='CUST_INFO_IDX';
SELECT T.STATUS,T.* FROM USER_IND_PARTITIONS T 
    WHERE T.INDEX_NAME='CUST_INFO_ARC_IDX';

--交换完成后需要将索引重建一下，重建分区索引需要指定分区。
ALTER INDEX CUST_INFO_IDX REBUILD;
ALTER INDEX CUST_INFO_ARC_IDX REBUILD PARTITION CUST_INFO_ARC_20140202;

--现在再看一次，索引状态已经正常了。
SELECT T.STATUS,T.* FROM USER_INDEXES T 
    WHERE INDEX_NAME='CUST_INFO_IDX';
SELECT T.STATUS,T.* FROM USER_IND_PARTITIONS T 
    WHERE T.INDEX_NAME='CUST_INFO_ARC_IDX';

--其实做完大量的数据移动后，应该重新收集统计信息
--以便查询优化器更好的工作。请注意这里要输入用户名，自行替换。
BEGIN DBMS_STATS.GATHER_TABLE_STATS('MART','CUST_INFO');END;
BEGIN DBMS_STATS.GATHER_TABLE_STATS('MART','CUST_INFO_ARC');END;

--归档表可以利用程序按照策略方便的删除更旧的不用的数据。语句如下。
ALTER TABLE CUST_INFO_ARC 
    DROP PARTITION CUST_INFO_ARC_INIT;

--看看分区已经只剩下最新的一个了 
--另外最后一个分区是无法删除的，可以自己尝试。
SELECT * FROM USER_TAB_PARTITIONS 
    WHERE TABLE_NAME='CUST_INFO_ARC';

--实验到此结束 删掉所有的表
DROP TABLE CUST_INFO;
DROP TABLE CUST_INFO_ARC;

何枝可依

Oracle中使用分区表来归档冷数据