关于interval partitioning(r3笔记65天)
关于interval partitioning(r3笔记65天)
今天一个开发的同事问我一个问题，说在系统中目前需要一个临时的解决方案，定义了一张表，目前想设定为分区表，因为会和外围系统交互产生大量的数据，所以需要按照小时来做分区，按照目前的数据情况，可能每个小时的数据量都在几十万~百万.
如果按照常规思路来说，那这个需求基本上时候不可能实现的.
一个是分区的限制数，按照小时来分区，那么1天（24小时)*365(天数）=8760个分区，一年需要产生这么多的分区，支持这么多的分区，性能肯定会受到影响，不排除bug的可能。
第二个是如果在业务闲时，如果事先创建了分区，但是没有数据就显得有些浪费了，按照目前的系统交互情况，还不能完全肯定精确的时间点来限定什么时候产生数据，什么时候不产生数据。
第三个是需要定期的去清理分区的数据，对于限定时间内的分区数据进行清理。如果分区规则不合理，就可能会影响到其它的数据。比如按照小时来分区，设定24个分区，那么做数据清理的时候就很麻烦，如果删错了数据是很严重的。
基于以上三点，可以考虑使用interval partitioning来实现，这个特性是在11g之后推出的，是对range partition的扩展。也是自动化分区的一个大胆尝试。
我使用了如下的例子来简单说明。
--创建序列，来递增作为主键id.
CREATE SEQUENCE TEST_ID_SEQ START WITH 1 INCREMENT BY 1 CACHE 1000 NOCYCLE MAXVALUE 999999999999999;
--创建表,创建了分区表。亮点就是标黄的部分，一下子少了很多的内容。为了测试快速简单，我使用分钟来作为间隔自动生成分区。
CREATE TABLE TEST_NEW_PARTITION
(
TEST_SEQ_ID NUMBER(16),
SYS_CREATION_DATE DATE,
MEMO VARCHAR2(100)
)
PARTITION BY RANGE (SYS_CREATION_DATE) INTERVAL(NUMTODSINTERVAL (1, 'MINUTE'))
(
PARTITION START_PART VALUES LESS THAN (TO_DATE('2014-11-01', 'YYYY-MM-DD')) NOLOGGING
) ;--查看分区的情况。
SQL> COL PARTITION_NAME FORMAT A30
SQL> SET LONG 9999
SQL> select partition_name,high_value from user_tab_partitions where table_name='TEST_NEW_PARTITION'
PARTITION_NAME HIGH_VALUE
------------------------------ ------------------------------------------------------------------------------------------------------------------------------------------------------
START_PART TO_DATE(' 2014-11-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')尝试插入两条记录来看看，中间需要有一些时间间隔。
SQL> insert into TEST_NEW_PARTITION values(TEST_ID_SEQ.nextval,sysdate,'a');
1 row created.
间隔一会
SQL> insert into TEST_NEW_PARTITION values(TEST_ID_SEQ.nextval,sysdate,'a');
1 row created.
查看分区情况，可以看到产生了两个新的分区，分区命名是按照系统自动生成的，high_value中可以看到相应的分区间隔点。
在13:05~13:29之间的那20多分钟时间内，因为没有数据，这里就没有生成分区。
SQL> select partition_name,high_value from user_tab_partitions where table_name='TEST_NEW_PARTITION';
PARTITION_NAME HIGH_VALUE
------------------------------ ------------------------------------------------------------------------------------------------------------------------------------------------------
START_PART TO_DATE(' 2014-11-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')
SYS_P26223 TO_DATE(' 2014-11-25 13:05:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')
SYS_P26224 TO_DATE(' 2014-11-25 13:29:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')然后我们再尝试插入一条记录。
SQL> insert into TEST_NEW_PARTITION values(TEST_ID_SEQ.nextval,sysdate,'b');
1 row created.
分区情况如下。
SQL> select partition_name,high_value from user_tab_partitions where table_name='TEST_NEW_PARTITION';
PARTITION_NAME HIGH_VALUE
------------------------------ ------------------------------------------------------------------------------------------------------------------------------------------------------
START_PART TO_DATE(' 2014-11-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')
SYS_P26223 TO_DATE(' 2014-11-25 13:05:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')
SYS_P26224 TO_DATE(' 2014-11-25 13:29:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')
SYS_P26225 TO_DATE(' 2014-11-25 13:30:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')大体对于interval partitioning已经做了简单的介绍，在这个基础上再做些补充。主要有以下几点
#1 关于间隔分区的支持，目前可以采用如下的两种形式（日期和数字)
numtoyminterval ( n, { 'YEAR'|'MONTH'})
numtodsinterval ( n, { 'DAY'|'HOUR'|'MINUTE'|'SECOND'}) 使用日期形式来间隔分区
Interval (number) 按照数字来间隔分区
#2 关闭/开启自动化分区的功能
关闭功能其实很简单，就是把interval的部分设为空。
SQL> alter table test_new_partition set interval();
Table altered.
然后再尝试插入一条记录。就会发现被reject了。
SQL> insert into TEST_NEW_PARTITION values(TEST_ID_SEQ.nextval,sysdate,'a');
insert into TEST_NEW_PARTITION values(TEST_ID_SEQ.nextval,sysdate,'a')
*
ERROR at line 1:
ORA-14400: inserted partition key does not map to any partition
如果需要开启也是类似，设定interval的值就可以了。比如下面的命令。
SQL> ALTER TABLE TEST_NEW_PARTITION SET INTERVAL(NUMTODSINTERVAL (1, 'MINUTE'));
Table altered.
#3 关于过期分区的清理
可以使用如下的简单pl/sql来进行简单的清理。目前设定的间隔是分钟，我们假定删除100分钟以前的分区数据。
set serveroutput on buffer 1000000
declare
v_date date;
begin
for rec in (select a.table_name, a.partition_name, high_value, 100 as retention from user_tab_partitions a where table_name='TEST_NEW_PARTITION' and partition_name<>'START_PART' order by 1,2)
loop
begin
execute immediate 'select ' || rec.high_value || ' from dual' into v_date;
if (v_date < (sysdate - rec.retention/24/60 ))
then
dbms_output.put_line ('Dropping partition '||rec .table_name||'.'||rec.partition_name||' - '||to_char(v_date,'YYYY-MM-DD'));
execute immediate 'alter table ' || rec.table_name || ' drop partition ' || rec.partition_name;
dbms_output.put_line ('Dropping partition '||rec.table_name||'.'||rec.partition_name||' - Dropped');
end if ;
end;
end loop;
end;
/运行脚本后的结果如下：
Dropping partition TEST_NEW_PARTITION.SYS_P26223 - 2014-11-25
Dropping partition TEST_NEW_PARTITION.SYS_P26223 - Dropped
PL/SQL procedure successfully completed.
#4 storage的补充。
比如我们希望把分区的数据按照分区(POOL_DATA,POOL_IX,TOOLS)的形式进行存储，就可以使用set store来设定。
SQL> alter table TEST_NEW_PARTITION set store in (POOL_DATA,POOL_IX,TOOLS);
Table altered.
这样新增的分区就会分区按照顺序循环在上面的3个表空间中分布。
#5 分区的重命名
目前还没有发现这个特性能够指定分区命名。如果需要按照要求进行修改，就可以使用下面的形式来修改。
alter table xxx rename partition xxxx to xxxx
总之这个新特性显得分区很动态，确实能省事不少，不过对于核心系统来说使用还是需要谨慎，毕竟我们需要管理数据，让数据在控制之内，如果核心表出现问题还是很要命的。对于一些优先级不高的模块可以尝试一下，从目前的情况来看效果还是不错的。