Extending existing partitioning

Question

Below the simplified structure of a table:

create table customer(
incident_id number,
customer_id number,
customer_name varchar2(400),
sla_id number
failure_start_date date,
failure_end_date date, 
churn_flag number, -- 0 or 1 
active number,  -- 0 or 1
constraint pk_incident_id primary key (incident_id))
PARTITION BY LIST (active)
SUBPARTITION BY LIST (churn_flag)
SUBPARTITION  TEMPLATE  
  ( SUBPARTITION sp_churn_flag_1 VALUES (1)
  , SUBPARTITION sp_churn_flag_0 VALUES (0)
  )

  (PARTITION sp_active_1 values (1)
  , PARTITION sp_active_0 VALUES (0)
  )
,
ENABLE ROW MOVEMENT COMPRESS FOR QUERY LOW;

Now I need to add additonally to the existing Composite-List-Partition an Interval-Range-Partitioning, in order to partitionate the data by month (failure_starte_date - YYYYMM). The table contains data from 200701 up to now (201511). Failure_start_date < 2013 should be partitionied into one partition for older data. All newer months should have an dedicated partition, whereas partitions for upcoming months shall be created automatically. How can this be integrating into the already existing partitoning?

Answer 1

You cannot do it exactly the way you want. Partitioning strategies are limited in two relevant ways: first, composite strategies can only have two levels (you need 3) and second, interval partitioning, when used in a composite strategy must be at the top level.

Here is the closest legal thing to what you want:

CREATE TABLE matt_customer
(
  incident_id          NUMBER,
  customer_id          NUMBER,
  customer_name        VARCHAR2 (400),
  sla_id               NUMBER,
  failure_start_date   DATE,
  failure_end_date     DATE,
  churn_flag           VARCHAR2 (1),                                                                           -- 0 or 1
  active               VARCHAR2 (1),                                                                           -- 0 or 1
  active_churn_flags   VARCHAR2 (2) GENERATED ALWAYS AS (active || churn_flag) VIRTUAL,
  CONSTRAINT pk_incident_id PRIMARY KEY (incident_id)
)
PARTITION BY RANGE
  (failure_start_date)
  INTERVAL ( NUMTOYMINTERVAL (1, 'MONTH') )
  SUBPARTITION BY LIST
    (active_churn_flags)
    SUBPARTITION TEMPLATE (
      SUBPARTITION sp_ac_00 VALUES ('00'),
      SUBPARTITION sp_ac_01 VALUES ('01'),
      SUBPARTITION sp_ac_10 VALUES ('10'),
      SUBPARTITION sp_ac_11 VALUES ('11'))
  (PARTITION customer_old VALUES LESS THAN (TO_DATE ('01-JAN-2013', 'DD-MON-YYYY')))
ENABLE ROW MOVEMENT
--COMPRESS FOR QUERY LOW;
;

This uses interval-list partitioning, and uses a virtual column to combine your active and churn_flag columns into one (I turned those columns into VARCHAR2(1) for simplicity.

To make use of partition pruning, your queries would need to be modified to select active_churn_flags = '01' for example, instead of specifying values for active and churn_flag independently.