Hello,
Please advise on how to create List Partition with Range Sub-Partition.
Also on the Range Sub-Partition like to have name custom defined based on each day like DEL_AUG_082019.
CREATE TABLE "DBB_USER"."STG_ES_STS"
(
"CS_ID" CHAR(7), "CWIN" NUMBER(9),
"PGM_TYP_CD" CHAR(2),
"ES_STS_ID" NUMBER(9),
"BGN_DT" DATE,
"END_DT" DATE,
"CRT_USR_ID" VARCHAR2(15),
"CRT_DTM" DATE,
"UPD_USR_ID" VARCHAR2(15),
"UPD_DTM" DATE,
"HIST_IND" CHAR(1),
"LAST_RTRV_DT" DATE,
"SRC_CITY_CD" VARCHAR2(2),
CONSTRAINT "STG_XPK_ES_STS_RSN"
PRIMARY KEY ("CS_ID", "CWIN", "PGM_TYP_CD", "ES_STS_ID", "ES_RSN_ID", "SRC_CITY_CD", "OP_TIME") VALIDATE )
TABLESPACE "DBB_USER" PCTFREE 10 INITRANS 1 MAXTRANS 255 STORAGE ( BUFFER_POOL DEFAULT)
PARTITION BY LIST ("SRC_CITY_CD")
(PARTITION "DEL" VALUES ('34') TABLESPACE "DBB_USER" PCTFREE 10 INITRANS 1 MAXTRANS 255 STORAGE ( BUFFER_POOL DEFAULT) ,
PARTITION "MUM" VALUES ('07') TABLESPACE "DBB_USER" PCTFREE 10 INITRANS 1 MAXTRANS 255 STORAGE ( BUFFER_POOL DEFAULT) ,
PARTITION "CAL" VALUES ('57') TABLESPACE "DBB_USER" PCTFREE 10 INITRANS 1 MAXTRANS 255 STORAGE ( BUFFER_POOL DEFAULT) ,
PARTITION "CHN" VALUES ('42') TABLESPACE "DBB_USER" PCTFREE 10 INITRANS 1 MAXTRANS 255 STORAGE ( BUFFER_POOL DEFAULT) ,
PARTITION "UNK" VALUES ('-1') TABLESPACE "DBB_USER" PCTFREE 10 INITRANS 1 MAXTRANS 255 STORAGE ( BUFFER_POOL DEFAULT) )
PARALLEL 4 ENABLE ROW MOVEMENT
Sample Data:
TABLE_OWNER TABLE_NAME PARTITION_NAME SUBPARTITION_NAME
-------------------- ----------------------------------- -------------------- ------------------------------
DBB_USER STG_ES_STS DEL DEL_AUG_012019
DBB_USER STG_ES_STS DEL DEL_AUG_022019
DBB_USER STG_ES_STS DEL DEL_AUG_032019
DBB_USER STG_ES_STS DEL DEL_AUG_042019
DBB_USER STG_ES_STS DEL DEL_AUG_052019
DBB_USER STG_ES_STS DEL DEL_AUG_092019
DBB_USER STG_ES_STS MUM DEL_AUG_012019
DBB_USER STG_ES_STS MUM DEL_AUG_022019
DBB_USER STG_ES_STS MUM DEL_AUG_032019
DBB_USER STG_ES_STS MUM DEL_AUG_042019
DBB_USER STG_ES_STS MUM DEL_AUG_052019
DBB_USER STG_ES_STS MUM DEL_AUG_092019
Here's an example to get you going
SQL> CREATE TABLE STG_ES_STS
2 (
3 CS_ID CHAR(7), CWIN NUMBER(9),
4 PGM_TYP_CD CHAR(2),
5 ES_STS_ID NUMBER(9),
6 BGN_DT DATE,
7 END_DT DATE,
8 CRT_USR_ID VARCHAR2(15),
9 CRT_DTM DATE,
10 UPD_USR_ID VARCHAR2(15),
11 UPD_DTM DATE,
12 HIST_IND CHAR(1),
13 LAST_RTRV_DT DATE,
14 SRC_CITY_CD VARCHAR2(2)
15 )
16 PARTITION BY LIST (SRC_CITY_CD)
17 subpartition by range ( BGN_DT )
18 (PARTITION DEL VALUES ('34')
19 (SUBPARTITION del_aug_01 VALUES less than ( date '2019-08-02'),
20 SUBPARTITION del_aug_02 VALUES less than ( date '2019-08-03'),
21 SUBPARTITION del_aug_03 VALUES less than ( date '2019-08-04'),
22 SUBPARTITION del_aug_04 VALUES less than ( date '2019-08-05')
23 ),
24 PARTITION MUM VALUES ('07')
25 (SUBPARTITION mum_aug_01 VALUES less than ( date '2019-08-02'),
26 SUBPARTITION mum_aug_02 VALUES less than ( date '2019-08-03'),
27 SUBPARTITION mum_aug_03 VALUES less than ( date '2019-08-04'),
28 SUBPARTITION mum_aug_04 VALUES less than ( date '2019-08-05')
29 ),
30 PARTITION CAL VALUES ('57')
31 (SUBPARTITION cal_aug_01 VALUES less than ( date '2019-08-02'),
32 SUBPARTITION cal_aug_02 VALUES less than ( date '2019-08-03'),
33 SUBPARTITION cal_aug_03 VALUES less than ( date '2019-08-04'),
34 SUBPARTITION cal_aug_04 VALUES less than ( date '2019-08-05')
35 ),
36 PARTITION CHN VALUES ('42')
37 (SUBPARTITION chn_aug_01 VALUES less than ( date '2019-08-02'),
38 SUBPARTITION chn_aug_02 VALUES less than ( date '2019-08-03'),
39 SUBPARTITION chn_aug_03 VALUES less than ( date '2019-08-04'),
40 SUBPARTITION chn_aug_04 VALUES less than ( date '2019-08-05')
41 ),
42 PARTITION UNK VALUES ('-1')
43 ) ;
Table created.