Oracle: using Automatic List Partitioning with Range Subpartitioning

Question

I want to use a partitioning method for dividing my table into two dimension:

1. The first dimension is a list of keys. The list can grow over time and i do not want to need a DBA for adding partitions if the list of keys will be extended. Therefore i want to use Automatic List Partitioning.

2. The second dimension is a daily range of a date column.

Here is my example which gives me an ORA-14179

CREATE TABLE PartitionedTable
( 
  id              number,
  PartitionKey    number,
  created         date
) 
PARTITION BY LIST (PartitionKey) AUTOMATIC
SUBPARTITION BY RANGE (created) INTERVAL (NUMTODSINTERVAL(1,'DAY'))
( PARTITION p_PartitionKey VALUES (1)
  ( SUBPARTITION p_created VALUES LESS THAN (TO_DATE('01-JAN-2000','dd-MON-yyyy'))
  )
);

I am using Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

Is there an option how I can create this two dimensional partitioned table with a minimum need of effort if new keys and new dates are inserted?

Answer 1

For all who are interested, i am currently using the following strategy which delivers all my requirements:

CREATE TABLE PartitionedTable
( 
  id              number,
  PartitionKey    number,
  created         date,
  trunc_created date generated always as (trunc(created)) virtual
) 
PARTITION BY LIST (PartitionKey, trunc_created) AUTOMATIC
(  
  PARTITION PDEFAULT VALUES (1, to_date('01.01.2000', 'DD.MM.YYYY'))
);

Answer 2

Both interval and list automatic partitioning are not supported at the subpartition level.
Maybe it's better to do this without subpartitions. It's also possible to use multiple keys or virtual columns for automatic list partitioning. Consider following demo:

create table parttab (
    id number, key number, created date, 
    partkey varchar (16) as (to_char (key, 'FM099999')||'-'||to_char (created, 'yyyymmdd')) virtual 
)
partition by list (partkey) automatic (partition pdefault values ('000000-19000101'))
;

insert into parttab (id, key, created) 
    select rownum id, trunc (rownum/5)+1 key, date'2019-01-01' + trunc (rownum/4)
    from xmlTable ('1 to 6')
; 

select partition_name, high_value, num_rows
from user_tab_partitions
where table_name = upper ('parttab') 
;

The newly created partitions look something like this:

PARTITION_NAME   HIGH_VALUE           NUM_ROWS
---------------- ------------------ ----------
PDEFAULT         '000000-19000101'           0
SYS_P1588        '000001-20190101'           3
SYS_P1589        '000001-20190102'           1
SYS_P1590        '000002-20190102'           2

Answer 3

The problem is:

Interval partitioning is not supported at the subpartition level.

The same applies for automatic list subpartitioning - this is unsupported too. So you can't solve this by using interval-automatic partitioning. So whatever you do, you're going to need to do subpartition maintenance to split values out.

If your goal is minimizing (sub)partition maintenance, you may be better with an interval-list partitioned table. With a default partition for the list values.

You can change the subpartition template anytime. This defines which subpartitions the database creates when you add a new top-level partition.

For example, this creates an interval-list table:

create table partitionedtable ( 
  id              number,
  partitionkey    number,
  created         date
) 
partition by range (created) interval (numtodsinterval(1,'day'))
subpartition by list (partitionkey) 
subpartition template  (
  subpartition p1 values ( 1 ),
  subpartition pdef values ( default )
) ( 
  partition p2000 values less than ( date'2019-01-01' )
);

insert into partitionedtable values ( 1, 1, date'2019-01-02' );
insert into partitionedtable values ( 1, 2, date'2019-01-02' );

The value 2 goes in the default subpartition.

You spot this and update the template to include a subpartition for this:

alter table partitionedtable
  set subpartition template (
    subpartition p1 values ( 1 ),
    subpartition p2 values ( 2 ),
    subpartition pdef values ( default )  
  );

insert into partitionedtable values ( 1, 1, date'2019-01-03' );
insert into partitionedtable values ( 1, 2, date'2019-01-03' );

select partition_name, subpartition_name, high_value 
from   user_tab_subpartitions;

PARTITION_NAME    SUBPARTITION_NAME    HIGH_VALUE   
P2000             P2000_P1             1             
P2000             P2000_PDEF           default       
SYS_P772          SYS_SUBP771          default       
SYS_P772          SYS_SUBP770          1             
SYS_P776          SYS_SUBP773          1             
SYS_P776          SYS_SUBP774          2             
SYS_P776          SYS_SUBP775          default   

The new partition (SYS_P776) has a subpartition with the value 2. The existing partitions are unchanged. You'll need to split SYS_P772 if you want rows with value 2 in their own subpartition here.

Assuming created => insert date, this means you only need to split subpartitions for new partitionkeys between their first insert & the date you change the template.

Whereas with automatic-range partitions, for each new partitionkey you need to manage new date subpartitions.

Flipping the partition scheme around like this has other implications though. So check whether this fits with your other reasons for partitioning (query performance, data archiving, ...) before going ahead.