INTERVAL分区（间隔分区）

这两天帮忙优化一个库，因为安全问题我就不贴图了，描述下情况就算了。情况是这样一个情况，大概什么情况呢，言归正传专门给你们换一行描述吧！

数据库io一直很高，但是看awr发现整体负载看上去也还好，服务器磁盘是个机械盘，每秒IO写相对来说有点偏高了。所以emc监控一直显示蓝色。继续分析主要是在三个近2亿的表上写入和删除花费了大量时间，看了下因为程序一个前台操作就会进行一个插入操作，所以没啥优化空间。删除日期是七天以前日期的历史数据，那是不是可以做分区然后直接删除分区。经过和业务方沟通，的确可以这样做。

那么问题来了，如何规划分区表呢，最好看肯定是根据日期来的，我找到了他之前删除的条件的列，但是根据那个日期列查询发现最近一小时数据量1个多亿，一小时以前的才4千万。这样算按天分区也没意义，可能得换哈希开并行的方案了。经过和业务方深入沟通，他那个字段专门用来记录删除时间的，但是这个时间很有意思，是在插入的时候就告诉你了具体删除的时间，所以所有时间都是创建时间的N天后的某一天。

根据上述情况准备做个INTERVAL分区，这时候不熟悉分区表的可能就提出一个疑问了。分区规则是以日为间隔，创建表时创建了存放当日数据的分区。请问:

1. 插入一条日期是10天前的数据，然后在插入一条日期是5天前的数据，会存放在哪个分区
2. 先插入一条日期是10天后的数据，然后在插入一条日期是5天后的数据，会存放在哪个分区

我先来回答这个问题，然后在通过一个测试语句演示该情况：

当日前的所有数据都存放在建表时的分区中，当日后的数据不管先后顺序都会为每一天创建一个分区。

验证阶段：

创建测试表

create table zkycs_ptb
(
pid number(10),
pname varchar2(30),
create_date date
) partition by range(create_date) interval (numtodsinterval(1,'day'))
(partition p_before_2024 values less than (to_date('2024-05-13','YYYY-MM-DD')),
partition p_before_20242 values less than (to_date('2024-05-19','YYYY-MM-DD'))
);
col TABLE_NAME format a15
col PARTITION_POSITION format a20
col PARTITION_NAME format a15
select TABLE_NAME,PARTITION_NAME,PARTITION_POSITION,HIGH_VALUE from user_tab_partitions;
TABLE_NAME PARTITION_NAME PARTITION_POSITION HIGH_VALUE
--------------- --------------- -------------------- --------------------------------------------------------------------------------
ZKYCS_PTB P_BEFORE_2024 1 TO_DATE(' 2024-05-13 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
ZKYCS_PTB P_BEFORE_20242 2 TO_DATE(' 2024-05-19 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA


插入不同时段的数据，看看是否会自动创建分区

insert into zkycs_ptb (pid, pname, create_date) values (1, '瑶瑶', TO_DATE ('2024-05-10 15:56:12','SYYYY-MM-DD HH24:MI:SS' ));
insert into zkycs_ptb (pid, pname, create_date) values (150, '倩倩', TO_DATE ('2024-05-17 15:56:12','SYYYY-MM-DD HH24:MI:SS' ));
insert into zkycs_ptb (pid, pname, create_date) values (2, '优优', TO_DATE ('2024-05-29 15:56:12','SYYYY-MM-DD HH24:MI:SS' ));
insert into zkycs_ptb (pid, pname, create_date) values (23, '点点', TO_DATE ('2024-05-22 15:56:12','SYYYY-MM-DD HH24:MI:SS' ));

col TABLE_NAME format a15
col PARTITION_POSITION format a20
col PARTITION_NAME format a15
select TABLE_NAME,PARTITION_NAME,PARTITION_POSITION,HIGH_VALUE from user_tab_partitions;
TABLE_NAME PARTITION_NAME PARTITION_POSITION HIGH_VALUE
--------------- --------------- -------------------- ------------------------------------------------------------------------------------------------------------------------------------------------------
ZKYCS_PTB P_BEFORE_2024 1 TO_DATE(' 2024-05-13 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
ZKYCS_PTB P_BEFORE_20242 2 TO_DATE(' 2024-05-19 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
ZKYCS_PTB SYS_P16934 3 TO_DATE(' 2024-05-23 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
ZKYCS_PTB SYS_P16933 4 TO_DATE(' 2024-05-30 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA


可以总结问题了，这个间隔分区，建表后它的分区就会以创建最大的范围为界限，界限一下丢在可以匹配的分区中，界限以外的为每天单独创建分区。（而且你会发现，新插的数据是建表时界限外但是当前界限内它会正常创建表同时还会吧PARTITION_POSITION的顺序搞对。）

再给你验证下，可以看出PARTITION_NAME的名字顺序是创建顺序，但是PARTITION_POSITION的顺序是根据范围排序的

insert into zkycs_ptb (pid, pname, create_date) values (23, '点点', TO_DATE ('2024-05-21 15:56:12','SYYYY-MM-DD HH24:MI:SS' ));

TABLE_NAME PARTITION_NAME PARTITION_POSITION HIGH_VALUE
--------------- --------------- -------------------- ------------------------------------------------------------------------------------------------------------------------------------------------------
ZKYCS_PTB P_BEFORE_2024 1 TO_DATE(' 2024-05-13 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
ZKYCS_PTB P_BEFORE_20242 2 TO_DATE(' 2024-05-19 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
ZKYCS_PTB SYS_P16935 3 TO_DATE(' 2024-05-22 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
ZKYCS_PTB SYS_P16934 4 TO_DATE(' 2024-05-23 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
ZKYCS_PTB SYS_P16933 5 TO_DATE(' 2024-05-30 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

总结分区表没问题，但是最后还是出了幺蛾子，为啥呢，分区字段有空值，他居然用空值表示这个数据永久不删，你说气不气。为啥不能有空值？我给你在演示一下

SQL> insert into zkycs_ptb (pid, pname) values (23, '点点');
insert into zkycs_ptb (pid, pname) values (23, '点点')

ORA-14300: 分区关键字映射到超出允许的最大分区数的分区



虽然文章事件是为了引出间隔分区，但是咱还是要有始有终。最终还是给了优化建议，经过细分析虽然磁盘不行，但是空间大小和CPU很给力，所以建议把删除的任务干掉，采用计划任务一天删除一次开16个并行，因为算下来平均一天才600w数据。第二个就是建议升级硬盘搞闪存卡吧，嘎嘎快。

然后就这样了，今天的flag算是完成了。欢迎指正！！！