Oracle 11g 按季分区interval parition

Oracle 11g 按季分区interval parition

interval 分区是oracle 11g引入的新技术，无需DBA预分配新分区，插入数据时系统会根据range列和已分配的分区自动判断新数据是否可以插入到已存在的分区中，如果不能满足插入已存在的分区，系统自动分配一个新分区来存放新插入的数据。

（1）按季度分区

希望通过间隔分区，创建一个按季度的分区：
间隔是3个月一个周期：
(NUMTOYMINTERVAL(3,‘month’))
脚本如下：

 CREATE TABLE DH.TEST_PART
    	(
    	ORDER_DATE DATE,
		REGION_ID NUMBER
    	)
		TABLESPACE TB_DATAHOUSE 
    	PARTITION BY RANGE (ORDER_DATE) INTERVAL (NUMTOYMINTERVAL(3,'month'))
    	STORE IN ("TB_PART_A", "TB_PART_B", "TB_PART_C", "TB_PART_D")
   	   (
	   PARTITION int_part1
   		   VALUES LESS THAN (TO_DATE ('2020-04-01', 'YYYY-MM-DD')),
   		PARTITION int_part2
   		   VALUES LESS THAN (TO_DATE ('2020-07-01', 'YYYY-MM-DD')),
   		PARTITION int_part3
   		   VALUES LESS THAN (TO_DATE ('2020-10-01', 'YYYY-MM-DD')),
   		PARTITION int_part4
   		   VALUES LESS THAN (TO_DATE ('2021-01-01', 'YYYY-MM-DD'))
		);

注意：
使用INTERVAL时需要注意月末日期的影响，实际上如果使用大于28号的日期来指定分区上界，就会得到一个错误：
ORA-14767: Cannot specify this interval with existing high bounds

因此都是使用的1号作为分区基础。

（2）测试间隔分区

插入数据：

begin
	for i in 0..30 loop
		insert into DH.TEST_PART values(add_months(to_date('2020-01-01','yyyy-mm-dd'),i),i);
	end loop;
	commit;
end;
/

前四个分区指定分区名称了，后面的分区系统自动命名，对比一下，看看表空间的分配结果：

SQL> select TABLE_NAME,PARTITION_NAME,TABLESPACE_NAME from user_tab_partitions where table_name='TEST_PART';

TABLE_NAME                     PARTITION_NAME                 TABLESPACE_NAME
------------------------------ ------------------------------ ------------------------------
TEST_PART                      INT_PART1                      TB_DATAHOUSE
TEST_PART                      INT_PART2                      TB_DATAHOUSE
TEST_PART                      INT_PART3                      TB_DATAHOUSE
TEST_PART                      INT_PART4                      TB_DATAHOUSE
TEST_PART                      SYS_P48                        TB_PART_A
TEST_PART                      SYS_P49                        TB_PART_B
TEST_PART                      SYS_P50                        TB_PART_C
TEST_PART                      SYS_P51                        TB_PART_D
TEST_PART                      SYS_P52                        TB_PART_A
TEST_PART                      SYS_P53                        TB_PART_B
TEST_PART                      SYS_P54                        TB_PART_C

11 rows selected


Executed in 0.229 seconds

看看分区内容，是否按季度分区：

SQL> select * from TEST_PART partition(INT_PART1);

ORDER_DATE   REGION_ID
----------- ----------
2020-1-1             0
2020-2-1             1
2020-3-1             2

Executed in 0.078 seconds


SQL> select * from TEST_PART partition(SYS_P48);

ORDER_DATE   REGION_ID
----------- ----------
2021-1-1            12
2021-2-1            13
2021-3-1            14

Executed in 0.086 seconds

（3）STORE IN 参数

interval partition中，分区的创建是由系统自动生成，这就存在一个问题：如何规划分区的存储,也就是系统自动分配的分区存放在哪些tablespace？
如果在store in后面标注上tablespaces的列表，新创建出的分区就会依次循环的均匀存放在各个分区上。
INT_PART1-4 是指定的分区名，在

SQL> select TABLE_NAME,PARTITION_NAME,TABLESPACE_NAME from user_tab_partitions where table_name='TEST_PART';

TABLE_NAME                     PARTITION_NAME                 TABLESPACE_NAME
------------------------------ ------------------------------ ------------------------------
TEST_PART                      INT_PART1                      TB_DATAHOUSE
TEST_PART                      INT_PART2                      TB_DATAHOUSE
TEST_PART                      INT_PART3                      TB_DATAHOUSE
TEST_PART                      INT_PART4                      TB_DATAHOUSE
TEST_PART                      SYS_P48                        TB_PART_A
TEST_PART                      SYS_P49                        TB_PART_B
TEST_PART                      SYS_P50                        TB_PART_C
TEST_PART                      SYS_P51                        TB_PART_D
TEST_PART                      SYS_P52                        TB_PART_A
TEST_PART                      SYS_P53                        TB_PART_B
TEST_PART                      SYS_P54                        TB_PART_C

TB_PART_A-D循环出现。