Background information

The Interval partition feature is added to the range partition table of Oracle 11g. For this type of range partitioning, you do not need to define MAXVALUE. Instead, Oracle dynamically assigns new partitions to contain data exceeding the range based on the step size.
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

Solutions

  • PolarDB O Edition currently partition type drop-down list partitioning and range partitioning (but does not support Interval partition), but can be the Interval partition according to the step size into the scope: Then, create corresponding partition table regularly in advance through the 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)
  • Define a job function that periodically creates partitions
    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;
  • Partition table added successfully.
    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=>
    Note poarDB-O support creating a job, you can automatically create the corresponding partition in advance on a regular basis.