范围分区在数据过期化处理中的应用
范围分区在数据过期化处理中的应用
在海量数据的数据库设计中，可能需要提前考虑数据库中数据存储的时间问题，或者叫做数据的过期化问题，它的意思是，由于数据量太大，在数据库中只保留特定时长的数据，比如1年，1年前的数据就需要做过期化（归档化）处理。
这时候范围分区就能发挥非常好的作用，就像上面的例子一样，我们可以将表做成每月1个分区，超过1年的分区可以按照用户的需求来进行处理。
对于过期的数据，通常有以下几种处理方式。
1．删除
处理历史数据的方式相当简单，直接删除过期的分区以及分区上的数据（如果每个分区分配一个表空间，那么可以连带表空间也删除，直接释放磁盘空间），释放空间。
在实际生产过程中，为了减少人为干预导致的失误，可以写一个定时任务来完成这样的操作，比如下面的例子。
还是上面的那个表，每个月一个分区。
CREATE
TABLE sale_data
(sale_id NUMBER(5),
salesman_name
VARCHAR2(30),
sales_amount NUMBER(10),
sales_date DATE)
PARTITION BY RANGE(sales_date)
(
PARTITION sales_2009_1 VALUES LESS
THAN(TO_DATE('01/02/2009','DD/MM/YYYY')),
PARTITION sales_2009_2 VALUES LESS
THAN(TO_DATE('01/03/2009','DD/MM/YYYY')),
PARTITION sales_2009_3 VALUES LESS
THAN(TO_DATE('01/04/2009','DD/MM/YYYY')),
PARTITION sales_2009_4 VALUES LESS
THAN(TO_DATE('01/05/2009','DD/MM/YYYY')),
PARTITION sales_2009_5 VALUES LESS
THAN(TO_DATE('01/06/2009','DD/MM/YYYY')),
PARTITION sales_2009_6 VALUES LESS
THAN(TO_DATE('01/07/2009','DD/MM/YYYY')),
PARTITION sales_2009_7 VALUES LESS THAN(TO_DATE('01/08/2009','DD/MM/YYYY')),
PARTITION sales_2009_8 VALUES LESS
THAN(TO_DATE('01/09/2009','DD/MM/YYYY')),
PARTITION sales_2009_9 VALUES LESS
THAN(TO_DATE('01/10/2009','DD/MM/YYYY')),
PARTITION sales_2009_10 VALUES LESS
THAN(TO_DATE('01/11/2009','DD/MM/YYYY')),
PARTITION sales_2009_11 VALUES LESS
THAN(TO_DATE('01/12/2009','DD/MM/YYYY')),
PARTITION sales_2009_12 VALUES LESS
THAN(TO_DATE('01/01/2010','DD/MM/YYYY')),
PARTITION sales_2010_1 VALUES LESS
THAN(TO_DATE('01/02/2010','DD/MM/YYYY'))
);
使用下面这样的一个存储过程，通过创建一个job，我们就可以定时删除1年以前的旧分区了。
CREATE OR REPLACE PROCEDURE drop_partition
AS
v_part_name varchar2(100);
--要添加分区表的名称前缀
v_over_time number; --过期时间间隔
v_err_num NUMBER; --ORA错误号
v_err_msg VARCHAR2(100); --错误描述
BEGIN
--得到时间最早的分区
select min(partition_name) into v_part_name
from user_tab_partitions where table_name='SALE_DATA';
--取得时间最早的分区到当前的时间间隔
select
months_between(sysdate,to_date(substr(min(partition_name),7,8),'yyyy-mm')) into v_over_time
from user_tab_partitions where table_name='SALE_DATA';
--删除最早的分区
if v_over_time>12 then
execute immediate 'alter table sale_data
drop partition '||v_part_name;
end if;
EXCEPTION
WHEN OTHERS THEN
v_err_num := SQLCODE;
v_err_msg := SUBSTR(SQLERRM, 1, 100);
dbms_output.put_line('执行错误: ' ||
v_err_num || '错误描述: ' || v_err_msg);
end drop_partition;
创建一个job，每天执行一次。
declare
job number;
begin
dbms_job.submit(job,'drop_partition;',sysdate,'sysdate+1');
end;
这样就可以定时删除过期的分区数据了。
2．移植到离线数据库中，作为历史数据处理
对于一些OLAP系统，业务上通常会考虑对数据进行过期化处理，将过期的数据移植到另外的数据库中保存，这种数据库称作离线数据库或者历史数据库。
将一部分业务切换到历史数据库中，然后对历史数据进行分析处理，比如数据挖掘、报表分析等。
这种数据库设计方式非常普遍，我们要讨论一下在线数据如何迁移到离线数据库中的问题，在这种情况下，分区就会发挥非常大的作用。
下面举一个具体的案例来说明这个过程。
我们称当前业务使用的数据库为在线数据库。比如，按照业务要求，在线数据库中只能存放1年的数据，再早的数据全部转移到离线数据库中变成历史数据。我们可以大致列出这样的一种思路：
（1）在设计在线数据库时，所有需要做数据过期化处理的表，按月创建分区。
（2）每个分区创建在单独的表空间上。
（3）将分区的表空间导出，并复制到离线数据库服务器上。
（4）在离线数据库服务器上将复制过来的数据导入到离线数据库中。
这样就完成了一个分区数据的过期化处理。
3．从数据库中移除，转入归档
在实际生产环境中，第三种对历史数据的处理方式是对历史数据归档，它是一种折中的方式，在成本上比离线数据库要低，但是还能保留历史数据。此处理方式需要一个海量的存储空间来存储过期的数据。
对于过期数据的归档处理，通常有两种方式：
（1）对于数据是通过对平面文件或者专有格式文件加载的方式入库的，比如数据仓库，则可以考虑在加载的同时，将这些数据文件归档到目标存储上面，完成数据备份。
比如有一个数据仓库系统，是通过SQL*Loader方式将数据文件加载入库的，那么在加载完成后，可以同时将这些文件传输到需要归档的存储介质上（可能是一个大的磁盘柜，也可能是磁带机）。以后如果需要使用这些数据，可以将它们复制回来，重新加载入库即可使用。
如图2-1所示是这种方式的示意图。
（2）从数据库中导出历史数据，然后归档。与上一种方式相比，这种方式显然对数据库的资源消耗比较大，但也算是一种方式。
通过传递表空间或者表导出的方式将数据库中的数据导出来，然后将这些文件归档到存储介质上。
如图2-2所示是这种方式的示意图。
本文节选自《让Oracle跑得更快2—基于海量数据的数据库设计与优化》一书。
图书详细信息:http://www.cnblogs.com/broadview/archive/2011/08/01/2123904.html
