Oracle 11g及以后版本 通过间隔分区实现按月创建表分区
在项目数据库设计过程中由于单表的数据量非常庞大，需要对表进行分区处理。由于表中的数据是历史交易，故按月分区，提升查询和管理。
由于之前对于表分区了解不多，为了实现上述功能查了很多资料，一开始的方向是通过Crontab调用Shell脚本来按月自动创建分区，或者使用Oracle的Job调用存储过程来自动创建分区。在研究上述两套方案的过程中，无意发现Oracle11g有间隔分区功能，对于使用Range分区的可以按年，月，日来自动生成分区。语法如下：
CREATE TABLE TEST_TABLE(
TABLE_ID VARCHAR2(32) DEFAULT SYS_GUID() NOT NULL PRIMARY KEY ,
SUB_DATE DATE NOT NULL ,
VALUE NUMBER(10) DEFAULT 0 NOT NULL
)
PARTITION BY RANGE(SUB_DATE)
INTERVAL(NUMTOYMINTERVAL(1,'MONTH'))
(
PARTITION P1 VALUES LESS THAN(TO_DATE('2018-04-01','YYYY-MM-DD'))
);
20148年4月1日前的数据会放入p1分区，5月1日后的数据每月只要有数据，就会自动创建一个分区。
我们可以分别插入几条记录，来看一下分区情况！
共插入7条数据，如下：
查询分区情况：
SELECT * FROM dba_segments t WHERE t.segment_name ='TEST_TABLE';
共5个分区：
关于间隔分区的小提示。
根据前人的经验，我们要按分区查找数据，需要这样：
SELECT * FROM TEST_TABLE PARTITION(SYS_P365)
--SYS_P365就是分区名
但是，我们要按分区查找，就需要先获得PARTITION_NAME（分区名），如何获得呢？
--先查找TEST_TABLE的HIGH_VALUE
SELECT TABLE_NAME, PARTITION_NAME, HIGH_VALUE FROM USER_TAB_PARTITIONS WHERE TABLE_NAME='TEST_TABLE'
此时，查找出来的HIGH_VALUE是LONG类型的，要解析他，我们需要一个函数的帮助。
先在数据库中创建函数：
--###自定义函数
CREATE OR REPLACE FUNCTION PARTITION_HIGH_VALUE (TABLE_NAME_IN VARCHAR2, PARTITION_NAME_IN VARCHAR2) RETURN VARCHAR2 IS
INCOMING VARCHAR2(32767);
RETURN_HOLD VARCHAR2(4000);
BEGIN
SELECT HIGH_VALUE INTO INCOMING FROM USER_TAB_PARTITIONS
WHERE PARTITION_NAME = PARTITION_NAME_IN
AND TABLE_NAME = TABLE_NAME_IN;
RETURN_HOLD := SUBSTR(INCOMING,11,10);
RETURN RETURN_HOLD;
END;
/
根据上面的函数，我们就可以根据日期查找到对应的分区名了。
SELECT TABLE_NAME, PARTITION_NAME FROM USER_TAB_PARTITIONS WHERE TABLE_NAME = 'TEST_TABLE' AND PARTITION_HIGH_VALUE(TABLE_NAME,PARTITION_NAME) = '2018-04-01'
是不是很麻烦？
不过，今天是上，我同事提醒了我，他说可以不用查找分区名，直接按时间来查找。。。
马上google，找到了相关的文章。。。
语法如下：
SELECT * FROM TEST_TABLE PARTITION FOR (TO_DATE('2018-04-01', 'YYYY-MM-DD'))
豁然开朗。。。。有没有？