背景说明

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,可以定期自动提前创建相应的分区。