转载

分区表的一个持续改进方案

今天看到一个同事发了一封邮件，是关于分区的，他说目前某个表的分区需要添加，为了保险起见，让我先添加三年的。这里折射出几个问题。
1.如果没有这位开发同学提醒，我还真不知道哪个表的分区数据会有问题
2.添加三年的分区，这个对于DBA来说是一个体力活，哪怕写脚本也是，本身维护起来就比较纠结。
3.三年后的分区谁来维护，我不敢拍着胸脯说我来，我想开发的同学也不会很肯定能够记住这件事情。
4.历史数据的清理，在这方面来看，如果没有业务的确认，DBA清理就无从谈起，如果都不确定，就是按兵不动。这是分区的优点也是缺点。
所以分区表在维护中还是有一些考量之处，还有几个潜在的风险，其中一个就是性能，如果累计数据越来越多，一个不经意的SQL语句可能就会造成全表扫描，在OLTP,OLAP业务中都是很严重的影响，当然OLTP业务更甚。
而目前对于我来说，要解决的问题的重点就是需要创建大量的分区，我还得考虑二月的那天是不是闰年，到底有多少天，如果为了省事直接给30天似乎也能说得通，不过很不严谨。批量写脚本生成可行，还有一个影响就是分区的段尽管没有数据，也会初始化有相应的段分区，三年按照天来计算，也是上千个分区了，这些空间也会提前被占用。
所以让我想到一个不错的解决方案就是interval partition了，这个特性我在之前的文章中也专门介绍过。我们再来重温一下。
#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.

现在的表bill_logout_cn就是这样一个分区表，存在一个默认分区，不过所幸里面没有数据，而从分区的数据来看应该是应用端做了控制，导致额外的数据没有插入进来。
我们要让这个表自动生成分区，就可以使用下面的语句，开启这个特性即可。
ALTER TABLE test.bill_logout_cn SET INTERVAL(NUMTODSINTERVAL (1, 'DAY'));
但是执行的时候还是发现有一些预先的必备条件。
SQL> ALTER TABLE test.bill_logout_cn SET INTERVAL(NUMTODSINTERVAL (1, 'DAY'));
ALTER TABLE test.bill_logout_cn SET INTERVAL(NUMTODSINTERVAL (1, 'DAY'))
*
ERROR at line 1:
ORA-14759: SET INTERVAL is not legal on this table.
对于这个错误，查看明细的错误信息。
[oracle@stat29 dbm_lite]$ oerr ora 14759
14759, 00000, "SET INTERVAL is not legal on this table."
// *Cause: SET INTERVAL clause was specified. ALTER TABLE SET INTERVAL is
//         only legal on a range partitioned table with a single partitioning
//         column. Additionally, this table cannot have a maxvalue partition
//         and cannot be the parent table for any reference partitioned tables.
// *Action: Use SET INTERVAL only on a valid table.
其实问题的原因就是这个分区表中存在默认的分区，我们删除默认分区。
SQL> alter table test.bill_logout_cn drop partition BILL_LOGOUT_DEFAULT;
Table altered.
然后开启interval 特性。
SQL> ALTER TABLE test.BILL_LOGOUT_CN SET INTERVAL(NUMTODSINTERVAL (1, 'DAY'));
Table altered.
间隔是以天为单位，这样一来就是一个"一劳永逸“的处理方案了。
不过回过头来，后面要删除分区数据，或者其它分区经的维护时，还会存在一些影响，就是interval partition的分区名是在是太不友好了。SYS开头而且生成规则看起来比较混乱。我们想要解决的好一些，那就是根据high_value来处理了。当然我一查网络上早有人意识到这个问题了。
以下来自网络。http://www.cnblogs.com/killkill/archive/2011/01/06/1928971.html

declare

v_sql varchar(400);

v_table_name user_tab_partitions.table_name%type;

v_partition_name user_tab_partitions.partition_name%type;

v_high_value varchar(200);

v_tmp_partition_name user_tab_partitions.partition_name%type;

cursor cur is

select

table_name ,

partition_name ,

high_value

from user_tab_partitions