How to split a default subpartition in a table using a subpartition template

Here is an example on how to split a subpartition utilizing a "subpartition template".

Before the change, the table looked as follows:

CREATE TABLE RECEIVED_DOCUMENTS
(
  DOCID                       VARCHAR2(160 BYTE)     NULL,
  FISCAL_YEAR                 NUMBER(4)             NULL,
  DOCUMENT_TYPE               VARCHAR2(100 CHAR)     NULL,
  DOCUMENT_NAME               VARCHAR2(1000 CHAR)     NULL,
  VALID                       CHAR(1 BYTE)          NULL,
  CREATED_TIMESTAMP           NUMBER(20)            NULL,
  VERSION                     NUMBER(20)            NULL,
  DOC_XML                     CLOB                  NULL,
  PERIOD                      VARCHAR2(1000 CHAR)     NULL,
  TRANSACTION_ID              VARCHAR2(1000 BYTE)     NULL
)
LOB (DOC_XML) STORE AS SECUREFILE RECEIVED_DOCUMENT_XML_LOB (TABLESPACE  LOB_DATA)
TABLESPACE USERS
PARTITION BY LIST (DOCUMENT_TYPE)
SUBPARTITION BY LIST (PERIOD)
SUBPARTITION TEMPLATE
  (SUBPARTITION SP_2014 VALUES ('2014') TABLESPACE DIV_2014,
   SUBPARTITION SP_2015 VALUES ('2015') TABLESPACE DIV_2015,
   SUBPARTITION SP_2016 VALUES ('2016') TABLESPACE DIV_2016,
   SUBPARTITION SP_DEFAULT VALUES (default) TABLESPACE DIV_DEFAULT
   )
  (  
  PARTITION CUSTOMS_DEC VALUES ('EU_CUST', 'NONEU_CUST'),
  PARTITION VAT_REF VALUES ('EU_VAT_REF', 'EES_VAT_REF','NONEU_VAT_REF'),
  PARTITION DIV_DOCUMENTS VALUES (default)
  )
;

First, alter the subpartition template. I also take the opportunity to create template for subpartitions in the years to come, up until 2020:

ALTER TABLE RECEIVED_DOCUMENTS
SET SUBPARTITION TEMPLATE
  (SUBPARTITION SP_2014 VALUES ('2014') TABLESPACE DIV_2014,
   SUBPARTITION SP_2015 VALUES ('2015') TABLESPACE DIV_2015,
   SUBPARTITION SP_2016 VALUES ('2016') TABLESPACE DIV_2016,
   SUBPARTITION SP_2017 VALUES ('2017') TABLESPACE DIV_2017,
   SUBPARTITION SP_2018 VALUES ('2018') TABLESPACE DIV_2018,
   SUBPARTITION SP_2019 VALUES ('2019') TABLESPACE DIV_2019,
   SUBPARTITION SP_2020 VALUES ('2020') TABLESPACE DIV_2020,
   SUBPARTITION SP_DEFAULT VALUES (default) TABLESPACE DIV_DEFAULT
  );

Then, split the resulting default subpartition DIV_DOCUMENTS_SP_DEFAULT, into multiple other subpartitions. In this example, I am splitting out rows in the DIV_DOCUMENTS_SP_DEFAULT subpartition which have their PERIOD value set to the string '2017':

ALTER TABLE RECEIVED_DOCUMENTS SPLIT SUBPARTITION DIV_DOCUMENTS_SP_DEFAULT INTO
     (  SUBPARTITION DIV_SP_2017 VALUES ('2017') TABLESPACE DIV_2017
      , SUBPARTITION DIV_SP_2017_01 VALUES ('2017-01') TABLESPACE DIV_2017
      , SUBPARTITION DIV_SP_2017_02 VALUES ('2017-02') TABLESPACE DIV_2017
      , SUBPARTITION DIV_SP_2017_03 VALUES ('2017-03') TABLESPACE DIV_2017
      , SUBPARTITION DIV_SP_2017_04 VALUES ('2017-04') TABLESPACE DIV_2017
      , SUBPARTITION DIV_SP_2017_05 VALUES ('2017-05') TABLESPACE DIV_2017
      , SUBPARTITION DIV_SP_2017_06 VALUES ('2017-06') TABLESPACE DIV_2017
      , SUBPARTITION DIV_SP_2017_07 VALUES ('2017-07') TABLESPACE DIV_2017
      , SUBPARTITION DIV_SP_2017_08 VALUES ('2017-08') TABLESPACE DIV_2017
      , SUBPARTITION DIV_SP_2017_09 VALUES ('2017-09') TABLESPACE DIV_2017
      , SUBPARTITION DIV_SP_2017_10 VALUES ('2017-10') TABLESPACE DIV_2017
      , SUBPARTITION DIV_SP_2017_11 VALUES ('2017-11') TABLESPACE DIV_2017
      , SUBPARTITION DIV_SP_2017_12 VALUES ('2017-12') TABLESPACE DIV_2017
      , SUBPARTITION DIV_DOCUMENTS_SP_DEFAULT) UPDATE INDEXES PARALLEL 8;

Notice in the above code that I also consider the other commonly used strings to indicate the year 2017. Of course, a row with a value of 'January 2017' in the PERIOD column will not find an appropriate partition according to the template, and thus end up in the default subpartition DIV_DOCUMENTS_SP_DEFAULT.

Let's add more partitions, this time to be prepared for the year 2018:

ALTER TABLE RECEIVED_DOCUMENTS SPLIT SUBPARTITION DIV_DOCUMENTS_SP_DEFAULT INTO
     (  SUBPARTITION DIV_SP_2018 VALUES ('2017') TABLESPACE DIV_2018
      , SUBPARTITION DIV_SP_2018_01 VALUES ('2017-01') TABLESPACE DIV_2018
      , SUBPARTITION DIV_SP_2018_02 VALUES ('2017-02') TABLESPACE DIV_2018
      , SUBPARTITION DIV_SP_2018_03 VALUES ('2017-03') TABLESPACE DIV_2018
      , SUBPARTITION DIV_SP_2018_04 VALUES ('2017-04') TABLESPACE DIV_2018
      , SUBPARTITION DIV_SP_2018_05 VALUES ('2017-05') TABLESPACE DIV_2018
      , SUBPARTITION DIV_SP_2018_06 VALUES ('2017-06') TABLESPACE DIV_2018
      , SUBPARTITION DIV_SP_2018_07 VALUES ('2017-07') TABLESPACE DIV_2018
      , SUBPARTITION DIV_SP_2018_08 VALUES ('2017-08') TABLESPACE DIV_2018
      , SUBPARTITION DIV_SP_2018_09 VALUES ('2017-09') TABLESPACE DIV_2018
      , SUBPARTITION DIV_SP_2018_10 VALUES ('2017-10') TABLESPACE DIV_2018
      , SUBPARTITION DIV_SP_2018_11 VALUES ('2017-11') TABLESPACE DIV_2018
      , SUBPARTITION DIV_SP_2018_12 VALUES ('2017-12') TABLESPACE DIV_2018
      , SUBPARTITION DIV_DOCUMENTS_SP_DEFAULT) UPDATE INDEXES PARALLEL 8;

This time, the split was considerably faster, since the resulting partitions for 2018 ended up with zero rows in them. Oracle can therefore perform a "fast split".

Read more in the 12.2 documentation