oracle周期成本取得,Oracle数据生命周期管理（data life cycle）完美解决方案

最新推荐文章于 2023-12-27 01:57:57 发布

转载最新推荐文章于 2023-12-27 01:57:57 发布 · 478 阅读

文章标签：

本文介绍了一种利用Oracle的PartitionTable和Interval功能，实现高效管理数据鲜度的方法。通过创建分区表，可以避免大量数据删除时的性能问题。文章提供了创建分区表的代码示例，并展示了如何通过DropPartition配合Function和Procedure自动删除超过6个月的数据，确保数据库仅保留最近6个月的数据。这种方法避免了Delete和Truncate的不足，实现了数据管理的完美方案。

之所以称之为完美，是因为不需要购买Oracle的生命周期管理组件，而是通过基本Partition

Table，Function，Procedure来实现。的的确确的实现。如下：

首先，让我来描述场景。数据库中只保存一定期限的数据，我愿意称之为数据鲜度。比如6个月，就是说我只保留当前6个月的数据，之前的数据要删除掉。设想下Log分析，每天都有Log生成，这些Log被保存在DB中，Log数据量是很大的而且要分析的对象都有一定期限比如6个月为期。那么之前的Log数据就要被删除。

在设计这个解决办法的时候，很多人会这样设想，按照天来写入Log数据，按月做一次Log的清除。疑问？单纯的Delete

From是不靠谱的，因为数据量大，这样的SQL文Performance不够理想。

也许还有人会这样。用Truncate，Insert。每天把Table

Truncate下，然后将最新鲜的6个月数据再写入。相比Delete，Truncate性能上非常好，但是Daily做Insert的负荷过重，也许你还会说，把Log打成CSV，再Load进DB中。可行，但是不够完美。

完美方案

在Oracle中建立Partition的Table。并且使用Interval的方式。具体请查看Oracle

Partition Table和Interval等说明。

代码：创建PartitionTable，将2015年之前的数据归为一个Partition

CREATE TABLE

TEST_HANG

(

MYID

VARCHAR2(8),

MYDATA

VARCHAR2(8), UPDATEDATE

DATE

)

NOCACHE

PARTITION BY RANGE (UPDATEDATE)

INTERVAL(numtoyminterval(1,'month')) ( PARTITION VALUES LESS THAN

(TO_DATE('2015-01-01','yyyy-mm-dd')));

接着插入数据：(请自己尝试)

例：

insert into

TEST_HANG values

('12345678','12345678',to_date('2015/01/01','yyyy/mm/dd'));

查看Partition信息：注意HighValue中的值。

select

TABLE_NAME,PARTITION_NAME,HIGH_VALUE from USER_TAB_PARTITIONS where

TABLE_NAME='TEST_HANG';

还可以查看一个Partition中的数据。

SELECT * FROM TEST_HANG

PARTITION(SYS_P266);

删除Partition和其中的数据。

ALTER TABLE TEST_HANG

DROP PARTITION SYS_P266;

※※※重点在这里，维持6个月的数据鲜度，用Drop

Partition的方式非常的高效。每日将Log写入DB，月底的batch自动的将6个月之前的Partition删除。非常完美。问题在于如何知道6个月之前生成的各个partition

name。如上我们需要知道SYS_P266这样的partition name。

用Function来取得HignValue中的日期值。

create or replace

function get_high_value_as_date

(

p_table_name in varchar2,

p_partition_name

in varchar2

) return date as

v_high_value

varchar2(1024);

v_date date;

begin

select high_value into

v_high_value from user_tab_partitions

where table_name =

upper(p_table_name)

and

partition_name = upper(p_partition_name);

execute immediate 'select '

|| v_high_value || ' from dual' into v_date;

return v_date;

end;

再写一个procedure来调用上面的Function，同时Loop来删除鲜度之外的数据。

CREATE OR REPLACE

PROCEDURE ORAH01.TEST_PROCE(tbName IN NVARCHAR2, minDate IN

DATE)

x_last_partition

exception;

pragma

exception_init(x_last_partition, -14758);

begin

for rec in (select

table_name, partition_name from user_tab_partitions where

table_name = tbName and

get_high_value_as_date(tbName, partition_name)

<= minDate) loop

begin

execute immediate 'alter table ' ||

rec.table_name || ' drop partition ' ||

rec.partition_name;

exception

when

x_last_partitionthen

null;

end;

end

loop;

end;

调用procedure来Drop

Partition

call

TEST_PROCE('TEST_HANG',to_date('2015/08/01','yyyy/mm/dd'));

上诉的代码均经过测试，所以直接就可以用。Blog排版太差。我把完整的code也一并上传了。有疑问给我留言吧。