How to create a range-list partitioned table, using interval partitioning and subpartition template

Note: I am placing the subpartitions, the initial partition, the table definition itself, and the LOB segments, in different tablespaces simply to illustrate this possiblity.

CREATE TABLE COUNTRY_TABLE
(
  ID                  VARCHAR2(36 CHAR)         NULL,
  DYEAR               NUMBER(4)                 NULL,
  COUNTRY             VARCHAR2(50 CHAR)         NULL,
  CREATED             TIMESTAMP(6)              DEFAULT systimestamp  NULL,
  CREATED_BY          VARCHAR2(30 CHAR)         DEFAULT NVL(SYS_CONTEXT( 'USERENV', 'CLIENT_IDENTIFIER' ), SYS_CONTEXT( 'USERENV', 'CURRENT_USER' )) NULL,
  LAST_CHANGED        TIMESTAMP(6)              DEFAULT systimestamp  NULL,
  LAST_CHANGED_BY     VARCHAR2(30 CHAR)         DEFAULT NVL(SYS_CONTEXT( 'USERENV', 'CLIENT_IDENTIFIER' ), SYS_CONTEXT( 'USERENV', 'CURRENT_USER' )) NULL,
  VERSION             NUMBER(5)                 DEFAULT 1 NULL,
  DOCUMENT            CLOB                      NULL,
  PERIOD AS (
                CAST ( CREATED AS DATE)
             ) VIRTUAL
)
LOB (DOCUMENT) STORE AS SECUREFILE (
 TABLESPACE  scott_data
)
PARTITION BY RANGE(PERIOD) INTERVAL ( NUMTOYMINTERVAL(1,'MONTH') )
SUBPARTITION BY LIST (COUNTRY)
 SUBPARTITION TEMPLATE
(
 SUBPARTITION SP_GERMAN VALUES ('GERMANY','SWITZERLAND','AUSTRIA') TABLESPACE USERS
,SUBPARTITION SP_FRENCH VALUES ('FRANCE','BELGIUM','LUXENBOURGH')  TABLESPACE SCOTT_DATA
,SUBPARTITION SP_SCANDI VALUES ('NORWAY','SWEDEN','DENMARK') TABLESPACE USERS
,SUBPARTITION SP_ENGLISH VALUES ('ENGLAND', 'WALES', 'SCOTLAND','IRELAND') TABLESPACE SCOTT_DATA
,SUBPARTITION SP_DEFAULT VALUES (default) TABLESPACE USERS
)
(
PARTITION PRE_2018 VALUES LESS THAN (TO_DATE('2018-01','YYYY-MM') ) TABLESPACE SCOTT_DATA
)
TABLESPACE USERS
ROW STORE COMPRESS ADVANCED
;

Analyze the table quickly;

EXEC DBMS_STATS.GATHER_TABLE_STATS (OWNNAME=>'SCOTT', TABNAME=>'COUNTRY_TABLE',GRANULARITY=>'AUTO');

After creation, the DD confirms the default partition with 5 subpartitions were created:

PARTITION_NAME	SUBPARTITION_NAME	NUM_ROWS	LAST_ANALYZED
PRE_2018	PRE_2018_SP_GERMAN	0	07.03.2019 10:41:21
PRE_2018	PRE_2018_SP_FRENCH	0	07.03.2019 10:41:21
PRE_2018	PRE_2018_SP_SCANDI	0	07.03.2019 10:41:21
PRE_2018	PRE_2018_SP_ENGLISH	0	07.03.2019 10:41:21
PRE_2018	PRE_2018_SP_DEFAULT	0	07.03.2019 10:41:21

Let's insert an "old" record, which sets the column CREATED to 2016-01:

INSERT INTO COUNTRY_TABLE (ID,DYEAR,COUNTRY,VERSION,CREATED)
VALUES ('8268b2c0-a526-11e8-8030-005056837631', 2017,'FRANCE',1, TO_TIMESTAMP('2016-01','yyyy-mm') );

Analyze the table with dbms_stats again, and we see that the PRE_2018 subpartition for countries with French language, now has one row:

PARTITION_NAME	SUBPARTITION_NAME	NUM_ROWS	LAST_ANALYZED
PRE_2018	PRE_2018_SP_GERMAN	0	07.03.2019 10:42:20
PRE_2018	PRE_2018_SP_FRENCH	1	07.03.2019 10:42:20
PRE_2018	PRE_2018_SP_SCANDI	0	07.03.2019 10:42:20
PRE_2018	PRE_2018_SP_ENGLISH	0	07.03.2019 10:42:20
PRE_2018	PRE_2018_SP_DEFAULT	0	07.03.2019 10:42:20

The rows was inserted into the PRE_2018_SP_FRENCH subpartition, as expected.

Now, let's insert a value which is greater than 2018-01, which I specified as the high value for my initial partition, called "PRE_2018":

-- Setting the created column to January 2ond, 2018:
INSERT INTO COUNTRY_TABLE (ID,DYEAR,COUNTRY,VERSION,CREATED)
VALUES ('8268b2c0-a526-11e8-8030-005056837631', 2018,'FRANCE',1, TO_TIMESTAMP('2018-01-02','YYYY-MM-DD') );
COMMIT;

This creates 4 new subpartitions with system generated names, with one row inserted into one of these:

   
PARTITION_NAME
   
SUBPARTITION_NAME
   
NUM_ROWS
   
LAST_ANALYZED
   
    
SYS_P2187
     
SYS_SUBP2182
     
0
     
07.03.2019 10:56:32
   
    
SYS_P2187
     
SYS_SUBP2183
     
1
     
07.03.2019 10:56:32
   
    
SYS_P2187
     
SYS_SUBP2184
     
0
     
07.03.2019 10:56:32
   
    
SYS_P2187
     
SYS_SUBP2185
     
0
     
07.03.2019 10:56:32
   
    
SYS_P2187
     
SYS_SUBP2186
     
0
     
07.03.2019 10:56:32
   
    
PRE_2018
     
PRE_2018_SP_GERMAN
     
0
     
07.03.2019 10:56:32
   
    
PRE_2018
     
PRE_2018_SP_FRENCH
     
1
     
07.03.2019 10:56:32
   
    
PRE_2018
     
PRE_2018_SP_SCANDI
     
0
     
07.03.2019 10:56:32
   
    
PRE_2018
     
PRE_2018_SP_ENGLISH
     
0
     
07.03.2019 10:56:32
   
    
PRE_2018
     
PRE_2018_SP_DEFAULT
     
0
     
07.03.2019 10:56:32
   

Another row for January 2018 will go into the same subpartition:

-- Setting created to January 3rd, 2018:
INSERT INTO COUNTRY_TABLE (ID,DYEAR,COUNTRY,VERSION,CREATED)
VALUES ('8268b2c0-a526-11e8-8030-005056837631', 2018,'FRANCE',1, TO_TIMESTAMP('2018-01-03','YYYY-MM-DD') );

PARTITION_NAME	SUBPARTITION_NAME	NUM_ROWS	LAST_ANALYZED
SYS_P2187	SYS_SUBP2183	2	07.03.2019 10:59:06

When inserting rows for a new month, addition 4 new subpartitions will be created, etc:

-- February 1st, 2018
INSERT INTO COUNTRY_TABLE (ID,DYEAR,COUNTRY,VERSION,CREATED)
VALUES ('8268b2c0-a526-11e8-8030-005056837631', 2018,'FRANCE',1, TO_TIMESTAMP('2018-02-01','YYYY-MM-DD') );

PARTITION_NAME	SUBPARTITION_NAME	NUM_ROWS	LAST_ANALYZED
SYS_P2205	SYS_SUBP2200	0	07.03.2019 11:00:47
SYS_P2205	SYS_SUBP2201	1	07.03.2019 11:00:47
SYS_P2205	SYS_SUBP2202	0	07.03.2019 11:00:47
SYS_P2205	SYS_SUBP2203	0	07.03.2019 11:00:47
SYS_P2205	SYS_SUBP2204	0	07.03.2019 11:00:47