为了保持查询的效率 我们应当将不用的数据放在归档表中
另一种场景是业务只需要当天的数据 但是可能需要在后台查询历史数据以供错误分析等用途
这里演示一下如何使用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;