oracle 分区表数据归档

随着系统的长期运行，积累的数据量也在不断的增长，随着业务的发展，增长的速度也在加剧。当积累到巨量的数据时，数据库的性能将急剧下降。一条查询SQL就有可能导致整个数据库僵死。为了保证查询的效率，我们需要适时的对几乎不访问的冷数据进行归档。

首先想到的方法是，读取待归档的数据， 插入到归档表，然后再将原表数据进行删除。实际测试的时候，发现在大数据的情况下， 归档过程非常慢，同时，delete数据后是不会主动释放表空间的。

因此，转而寻求其他更高效的方式，使用oracle的分区交换特性，按分区来进行数据归档。

oracle 分区交换

Oracle 交换分区你可以：

• 将一个分区(或子分区)转换成一个普通表。
• 将一个普通表转换成一个分区表的分区(或子分区)。
• 将一个哈希分区表转换成复合*哈希分区表的分区。
• 将一个复合*哈希分区表的分区转换成一个哈希分区表。
• 将一个(范围|列表)分区表转换成一个分区组合* 列表范围分区表。
• 将一个分区的复合*——(范围|列表)分区表转换到一个范围|列表分区表。

oracle在做分区交换的时候， 只涉及到数据字典的更新，因此执行非常快。

使用分区交换归档数据过程

1. 创建备份分区表
   创建与原表结构一致的备份分区表, 分区规则和分区名字也保持一致，为了分区交换执行更快，不为备份表创建键和字段约束。

2. 创建于原表一致的中间表（非分区表）

create table tmp_table_name as select * from table_name;

3. 分区交换

-- 先对原表分区与中间表进行分区交换
ALTER TABLE table_name
    EXCHANGE PARTITION partition_name WITH TABLE tmp_table_name;
-- 再对中间表与备份分区表进行分区交换
ALTER TABLE his_table_name
    EXCHANGE PARTITION partition_name WITH TABLE tmp_table_name;
-- 删除已经归档的分区
ALTER TABLE table_name PARTITION partition_name;

4. 重建索引
   分区交换，会导致分区表的全局索引失效，需要对索引进行rebuild。

-- 查询失效的索引，并组装索引重建sql
select 'alter index '||t.index_name||' rebuild online;' from user_indexes t where t.status='UNUSABLE'

执行索引重建SQL。

DONE。