本文介绍Interval的分区说明。
背景说明
Oracle 11g的范围分区表中新增的Interval分区特性，此种范围分区不需要定义MAXVALUE，Oracle会根据分区定义的步长来动态的分配新分区来容纳超过范围的数据。
create table BIGTABLE_LOG
(
record_date DATE,
col_1 VARCHAR2(2000),
col_2 VARCHAR2(2000)
)
PARTITION BY RANGE (record_date)
INTERVAL (numtodsinterval(1,'day'))
( PARTITION P1 VALUES LESS THAN (TO_DATE('2014-1-1', 'YYYY-MM-DD')));SQL> insert into BIGTABLE_LOG values (to_date('2013-1-1','YYYY-MM-DD'),'','');
1 row created.
SQL> insert into BIGTABLE_LOG values (to_date('2014-1-1','YYYY-MM-DD'),'','');
1 row created.
SQL> insert into BIGTABLE_LOG values (to_date('2014-1-2','YYYY-MM-DD'),'','');
1 row created.SQL> select * from BIGTABLE_LOG partition (P1);
RECORD_DATE COL_1 COL_2
------------ -------------------- --------------------
01-JAN-13
SQL> select * from BIGTABLE_LOG partition (SYS_P24);
RECORD_DATE COL_1 COL_2
------------ -------------------- --------------------
01-JAN-14
SQL> select * from BIGTABLE_LOG partition (SYS_P25);
RECORD_DATE COL_1 COL_2
------------ -------------------- --------------------
02-JAN-14解决方案
- PolarDB O引擎目前支持分区类型为列表分区和范围分区（但不支持Interval分区），但可以将Interval分区按步长转化为范围分区。然后通过job定期提前创建相应的分区表。
create table BIGTABLE_LOG ( record_date DATE, col_1 VARCHAR2(2000), col_2 VARCHAR2(2000) ) PARTITION BY RANGE (record_date) ( PARTITION P1 VALUES LESS THAN (TO_DATE('2014-1-1', 'YYYY-MM-DD')), PARTITION P2 VALUES LESS THAN (TO_DATE('2014-1-2', 'YYYY-MM-DD')), PARTITION P3 VALUES LESS THAN (TO_DATE('2014-1-3', 'YYYY-MM-DD')), ); van=> insert into BIGTABLE_LOG values (to_date('2013-1-1','YYYY-MM-DD'),'',''); INSERT 0 1 van=> insert into BIGTABLE_LOG values (to_date('2014-1-1','YYYY-MM-DD'),'',''); INSERT 0 1 van=> insert into BIGTABLE_LOG values (to_date('2014-1-2','YYYY-MM-DD'),'',''); INSERT 0 1 van=> select * from bigtable_log_p1; record_date | col_1 | col_2 --------------------+-------+------- 01-JAN-13 00:00:00 | | (1 row) van=> select * from bigtable_log_p2; record_date | col_1 | col_2 --------------------+-------+------- 01-JAN-14 00:00:00 | | (1 row) van=> select * from bigtable_log_p3; record_date | col_1 | col_2 --------------------+-------+------- 02-JAN-14 00:00:00 | | (1 row) - 定义定期创建分区的job函数
CREATE or replace FUNCTION add_partitions(tablename text, lessdate text,partitionname text)RETURNS text AS $$ DECLARE results text; DECLARE sql text; BEGIN results :='OK'; sql='ALTER TABLE '|| tablename ||' ADD PARTITION '|| partitionname ||' VALUES LESS THAN (TO_DATE('''||lessdate||''', ''YYYY-MM-DD''))'; execute sql; RETURN results; END; $$ LANGUAGE plpgsql; - 添加分区表成功
van=> select add_partitions('bigtable_log','2014-1-4','P4'); add_partitions ---------------- OK (1 row) van=> \d+ bigtable_log_p4 Table "public.bigtable_log_p4" Column | Type | Collation | Nullable | Default | Storage | Stats target | Description -------------+-----------------------------+-----------+----------+---------+----------+--------------+------------- record_date | timestamp without time zone | | | | plain | | col_1 | character varying(2000) | | | | extended | | col_2 | character varying(2000) | | | | extended | | Partition of: bigtable_log FOR VALUES FROM ('03-JAN-14 00:00:00') TO ('04-JAN-14 00:00:00') Partition constraint: ((record_date IS NOT NULL) AND (record_date >= '03-JAN-14 00:00:00'::timestamp without time zone) AND (record_date < '04-JAN-14 00:00:00'::timestamp without time zone)) van=>说明 poarDB-O支持创建job，可以定期自动提前创建相应的分区。