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;