oracle分区表统计分析拷贝,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;