Oracle 列表分区-范围分区

你好，

请建议如何创建带有范围子分区的列表分区。

同样在范围子分区上，例如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 

样本数据:

表 _ 所有者表 _ 名称分区 _ 名称子分区 _ 名称
-
2019年8月1日
2019年8月2日
2019年8月3日
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
2019年8月1日
2019年8月2日
2019年8月3日
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

这是一个让你前进的例子

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.