创建子分区表 partition by subpartition by

-- Create table
create table TF_DBD_SALE_HOSP_STRUC_U
(
  STATS_DT          NUMBER(8) not null,
  SALE_DT           NUMBER(8) not null,
  POSTN_WID         NUMBER(20) not null,
  PROD_WID          NUMBER(20) not null,
  HOSP_WID          NUMBER(20) not null,
  DBD_PRODLN_WID    NUMBER(20),
  METRIC_WID        NUMBER(20) not null,
  AREA_WID          NUMBER(20),
  DBD_PRODCAT_WID   NUMBER(20),
  DBD_BRAND_WID     NUMBER(20),
  MKT_BRAND_WID     NUMBER(20),
  ADJ_FLG           CHAR(1 CHAR) not null,
  SALE_STD_SALEDT   NUMBER(30,10),
  SALE_CURR_SALEDT  NUMBER(30,10),
  SALE_STD_STATSDT  NUMBER(30,10),
  SALE_CURR_STATSDT NUMBER(30,10),
  W_INSERT_DT       DATE,
  W_UPDATE_DT       DATE
)
tablespace OSALEDATA_TBS
  pctfree 10
  initrans 1
  maxtrans 255
  storage
  (
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
  )
   PARTITION BY RANGE ("STATS_DT") 
  SUBPARTITION BY RANGE ("SALE_DT") 
  SUBPARTITION TEMPLATE ( 
    SUBPARTITION "SP2007" VALUES LESS THAN ( 20080101 ), 
    SUBPARTITION "SP2008" VALUES LESS THAN ( 20090101 ), 
    SUBPARTITION "SP2009" VALUES LESS THAN ( 20100101 ), 
    SUBPARTITION "SP2010" VALUES LESS THAN ( 20110101 ), 
    SUBPARTITION "SP2011" VALUES LESS THAN ( 20120101 ), 
    SUBPARTITION "SP2012" VALUES LESS THAN ( 20130101 ), 
    SUBPARTITION "SP2013" VALUES LESS THAN ( 20140101 ), 
    SUBPARTITION "SP2014" VALUES LESS THAN ( 20150101 ), 
    SUBPARTITION "SP2015" VALUES LESS THAN ( 20160101 ), 
    SUBPARTITION "SP2016" VALUES LESS THAN ( 20170101 ), 
    SUBPARTITION "SP2017" VALUES LESS THAN ( 20180101 ), 
    SUBPARTITION "SP2018" VALUES LESS THAN ( 20190101 ),
    SUBPARTITION "SP9999" VALUES LESS THAN ( MAXVALUE ) ) 
 (PARTITION "P20140401"  VALUES LESS THAN (20140501)  TABLESPACE "OSALEDATA_TBS" , 
 PARTITION "P20140501"  VALUES LESS THAN (20140601) TABLESPACE "OSALEDATA_TBS" ,
 PARTITION "P20140601"  VALUES LESS THAN (20140701) TABLESPACE "OSALEDATA_TBS" , 
 PARTITION "P20140701"  VALUES LESS THAN (20140801)  TABLESPACE "OSALEDATA_TBS" , 
 PARTITION "P20140801"  VALUES LESS THAN (20140901)  TABLESPACE "OSALEDATA_TBS" , 
 PARTITION "P20140901"  VALUES LESS THAN (20141001)   TABLESPACE "OSALEDATA_TBS" , 
 PARTITION "P20141001"  VALUES LESS THAN (20141101)  TABLESPACE "OSALEDATA_TBS" , 
 PARTITION "P20141101"  VALUES LESS THAN (20141201)  TABLESPACE "OSALEDATA_TBS" , 
 PARTITION "P20141201"  VALUES LESS THAN (20150101)   TABLESPACE "OSALEDATA_TBS" , 
 PARTITION "P20150101"  VALUES LESS THAN (20150201)  TABLESPACE "OSALEDATA_TBS" , 
 PARTITION "P20150201"  VALUES LESS THAN (20150301)   TABLESPACE "OSALEDATA_TBS" , 
 PARTITION "P20150301"  VALUES LESS THAN (20150401)  TABLESPACE "OSALEDATA_TBS" , 
 PARTITION "P20150401"  VALUES LESS THAN (20150501)   TABLESPACE "OSALEDATA_TBS" , 
 PARTITION "P20150501"  VALUES LESS THAN (20150601)  TABLESPACE "OSALEDATA_TBS" , 
 PARTITION "P20150601"  VALUES LESS THAN (20150701)   TABLESPACE "OSALEDATA_TBS" , 
 PARTITION "P20150701"  VALUES LESS THAN (20150801)  TABLESPACE "OSALEDATA_TBS" , 
 PARTITION "P20150801"  VALUES LESS THAN (20150901)  TABLESPACE "OSALEDATA_TBS" , 
 PARTITION "P20150901"  VALUES LESS THAN (20151001)  TABLESPACE "OSALEDATA_TBS" , 
 PARTITION "P20151001"  VALUES LESS THAN (20151101)   TABLESPACE "OSALEDATA_TBS" , 
 PARTITION "P20151101"  VALUES LESS THAN (20151201)  TABLESPACE "OSALEDATA_TBS" , 
 PARTITION "P20151201"  VALUES LESS THAN (20160101)  TABLESPACE "OSALEDATA_TBS" , 
 PARTITION "P20160101"  VALUES LESS THAN (20160201)  TABLESPACE "OSALEDATA_TBS" , 
 PARTITION "P20160201"  VALUES LESS THAN (20160301)   TABLESPACE "OSALEDATA_TBS" , 
 PARTITION "P20160301"  VALUES LESS THAN (20160401)  TABLESPACE "OSALEDATA_TBS" , 
 PARTITION "P20160401"  VALUES LESS THAN (20160501)   TABLESPACE "OSALEDATA_TBS" , 
 PARTITION "P20160501"  VALUES LESS THAN (20160601)  TABLESPACE "OSALEDATA_TBS" , 
 PARTITION "P20160601"  VALUES LESS THAN (20160701)   TABLESPACE "OSALEDATA_TBS" , 
 PARTITION "P20160701"  VALUES LESS THAN (20160801)  TABLESPACE "OSALEDATA_TBS" , 
 PARTITION "P20160801"  VALUES LESS THAN (20160901)  TABLESPACE "OSALEDATA_TBS" , 
 PARTITION "P20160901"  VALUES LESS THAN (20161001)  TABLESPACE "OSALEDATA_TBS" , 
 PARTITION "P20161001"  VALUES LESS THAN (20161101)   TABLESPACE "OSALEDATA_TBS" , 
 PARTITION "P20161101"  VALUES LESS THAN (20161201)  TABLESPACE "OSALEDATA_TBS" , 
 PARTITION "P20161201"  VALUES LESS THAN (20170101)  TABLESPACE "OSALEDATA_TBS" , 
 PARTITION "P20170101"  VALUES LESS THAN (20170201)  TABLESPACE "OSALEDATA_TBS" , 
 PARTITION "P20170201"  VALUES LESS THAN (20170301)   TABLESPACE "OSALEDATA_TBS" , 
 PARTITION "P20170301"  VALUES LESS THAN (20170401)  TABLESPACE "OSALEDATA_TBS" , 
 PARTITION "P20170401"  VALUES LESS THAN (20170501)   TABLESPACE "OSALEDATA_TBS" , 
 PARTITION "P20170501"  VALUES LESS THAN (20170601)  TABLESPACE "OSALEDATA_TBS" , 
 PARTITION "P20170601"  VALUES LESS THAN (20170701)   TABLESPACE "OSALEDATA_TBS" , 
 PARTITION "P20170701"  VALUES LESS THAN (20170801)  TABLESPACE "OSALEDATA_TBS" , 
 PARTITION "P20170801"  VALUES LESS THAN (20170901)  TABLESPACE "OSALEDATA_TBS" , 
 PARTITION "P20170901"  VALUES LESS THAN (20171001)  TABLESPACE "OSALEDATA_TBS" , 
 PARTITION "P20171001"  VALUES LESS THAN (20171101)   TABLESPACE "OSALEDATA_TBS" , 
 PARTITION "P20171101"  VALUES LESS THAN (20171201)  TABLESPACE "OSALEDATA_TBS" , 
 PARTITION "P20171201"  VALUES LESS THAN (20180101)  TABLESPACE "OSALEDATA_TBS" , 
 PARTITION "P9999"  VALUES LESS THAN (MAXVALUE)  TABLESPACE "OSALEDATA_TBS"
 ) 
 ;
 
 
-- Add comments to the table 
comment on table TF_DBD_SALE_HOSP_STRUC_U
  is 'DBD当前架构分产品医院销售事实表';
-- Add comments to the columns 
comment on column TF_DBD_SALE_HOSP_STRUC_U.STATS_DT
  is '统计日期wid';
comment on column TF_DBD_SALE_HOSP_STRUC_U.SALE_DT
  is '销售日期wid';
comment on column TF_DBD_SALE_HOSP_STRUC_U.POSTN_WID
  is '职位wid';
comment on column TF_DBD_SALE_HOSP_STRUC_U.PROD_WID
  is '产品wid';
comment on column TF_DBD_SALE_HOSP_STRUC_U.HOSP_WID
  is '医院wid';
comment on column TF_DBD_SALE_HOSP_STRUC_U.DBD_PRODLN_WID
  is 'DBD产品组wid';
comment on column TF_DBD_SALE_HOSP_STRUC_U.METRIC_WID
  is '指标wid';
comment on column TF_DBD_SALE_HOSP_STRUC_U.AREA_WID
  is '地区wid(区县)';
comment on column TF_DBD_SALE_HOSP_STRUC_U.DBD_PRODCAT_WID
  is 'DBD产品归类wid';
comment on column TF_DBD_SALE_HOSP_STRUC_U.DBD_BRAND_WID
  is 'DBD产品品牌wid';
comment on column TF_DBD_SALE_HOSP_STRUC_U.MKT_BRAND_WID
  is '市场部产品品牌wid';
comment on column TF_DBD_SALE_HOSP_STRUC_U.ADJ_FLG
  is '是否是调整值';
comment on column TF_DBD_SALE_HOSP_STRUC_U.SALE_STD_SALEDT
  is '本期销售（按销售月份标准价）';
comment on column TF_DBD_SALE_HOSP_STRUC_U.SALE_CURR_SALEDT
  is '本期销售（按销售月份最新价）';
comment on column TF_DBD_SALE_HOSP_STRUC_U.SALE_STD_STATSDT
  is '本期销售（按统计月份标准价）';
comment on column TF_DBD_SALE_HOSP_STRUC_U.SALE_CURR_STATSDT
  is '本期销售（按统计月份最新价）';
comment on column TF_DBD_SALE_HOSP_STRUC_U.W_INSERT_DT
  is '数据仓库记录的创建时间';
comment on column TF_DBD_SALE_HOSP_STRUC_U.W_UPDATE_DT

  is '数据仓库记录的最后更新时间';

create bitmap index IDX_TF_DBD_SALE_HOSP_U_ADJ on TF_DBD_SALE_HOSP_STRUC_U (ADJ_FLG)  TABLESPACE OSALEIDX_TBSlocal;
create bitmap index IDX_TF_DBD_SALE_HOSP_U_AR on TF_DBD_SALE_HOSP_STRUC_U (AREA_WID) TABLESPACE OSALEIDX_TBS local;
create bitmap index IDX_TF_DBD_SALE_HOSP_U_DPC on TF_DBD_SALE_HOSP_STRUC_U (DBD_PRODCAT_WID) TABLESPACE OSALEIDX_TBS local;
create bitmap index IDX_TF_DBD_SALE_HOSP_U_HP on TF_DBD_SALE_HOSP_STRUC_U (HOSP_WID) TABLESPACE OSALEIDX_TBS local;
create bitmap index IDX_TF_DBD_SALE_HOSP_U_MT on TF_DBD_SALE_HOSP_STRUC_U (METRIC_WID) TABLESPACE OSALEIDX_TBS local;
create bitmap index IDX_TF_DBD_SALE_HOSP_U_POS on TF_DBD_SALE_HOSP_STRUC_U (POSTN_WID) TABLESPACE OSALEIDX_TBS local;
create bitmap index IDX_TF_DBD_SALE_HOSP_U_PRD on TF_DBD_SALE_HOSP_STRUC_U (PROD_WID) TABLESPACE OSALEIDX_TBS local;
create bitmap index IDX_TF_DBD_SALE_HOSP_U_SDT on TF_DBD_SALE_HOSP_STRUC_U (SALE_DT) TABLESPACE OSALEIDX_TBS local;
create bitmap index IDX_TF_DBD_SALE_HOSP_U_TDT on TF_DBD_SALE_HOSP_STRUC_U (STATS_DT) TABLESPACE OSALEIDX_TBS local;

select * from user_PART_TABLES 

select * from USER_TAB_PARTITIONS 

select distinct column_name from USER_PART_KEY_COLUMNS  

select distinct column_name from USER_SUBPART_KEY_COLUMNS

SELECT DBMS_METADATA.GET_DDL('TABLE', 'TF_DBD_SALE_HOSP_STRUC_U') FROM DUAL;
SELECT DBMS_METADATA.GET_DDL('INDEX', 'IDX_TF_DBD_SALE_HOSP_U_ADJ') FROM DUAL;