Thursday, 25 April 2013

Oracle SH Schema SQL Scripts

CREATE USER SH
    IDENTIFIED BY 
    ACCOUNT UNLOCK
;

CREATE TABLE SH.CHANNELS
    (
     CHANNEL_ID NUMBER  NOT NULL ,
     CHANNEL_DESC VARCHAR2 (20 BYTE)  NOT NULL ,
     CHANNEL_CLASS VARCHAR2 (20 BYTE)  NOT NULL ,
     CHANNEL_CLASS_ID NUMBER  NOT NULL ,
     CHANNEL_TOTAL VARCHAR2 (13 BYTE)  NOT NULL ,
     CHANNEL_TOTAL_ID NUMBER  NOT NULL
    )
        PCTFREE 10
        PCTUSED 40
        MAXTRANS 255
        TABLESPACE EXAMPLE
        NOLOGGING
        STORAGE (
        INITIAL 65536
        NEXT 1048576
        PCTINCREASE 0
        MINEXTENTS 1
        MAXEXTENTS 2147483645
        FREELISTS 1
        FREELIST GROUPS 1
        BUFFER_POOL DEFAULT
    )
;



COMMENT ON TABLE SH.CHANNELS IS 'small dimension table'
;

COMMENT ON COLUMN SH.CHANNELS.CHANNEL_ID IS 'primary key column'
;

COMMENT ON COLUMN SH.CHANNELS.CHANNEL_DESC IS 'e.g. telesales, internet, catalog'
;

COMMENT ON COLUMN SH.CHANNELS.CHANNEL_CLASS IS 'e.g. direct, indirect'
;

ALTER TABLE SH.CHANNELS
    ADD CONSTRAINT CHANNELS_PK PRIMARY KEY ( CHANNEL_ID ) NOVALIDATE ;


CREATE TABLE SH.COSTS
    (
     PROD_ID NUMBER (6)  NOT NULL ,
     TIME_ID DATE  NOT NULL ,
     PROMO_ID NUMBER (6)  NOT NULL ,
     CHANNEL_ID NUMBER  NOT NULL ,
     UNIT_COST NUMBER (10,2)  NOT NULL ,
     UNIT_PRICE NUMBER (10,2)  NOT NULL
    )
        PCTFREE 5
        PCTUSED 40
        MAXTRANS 255
        TABLESPACE EXAMPLE
        NOLOGGING
        STORAGE (
        PCTINCREASE 0
        MINEXTENTS 1
        MAXEXTENTS UNLIMITED
        FREELISTS 1
        FREELIST GROUPS 1
        BUFFER_POOL DEFAULT
    )
    DISABLE ROW MOVEMENT
    PARTITION BY RANGE ( TIME_ID )
    (
    PARTITION COSTS_1995 VALUES LESS THAN ( TO_DATE(' 1996-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN') )
        PCTFREE 0
        PCTUSED 40
        MAXTRANS 255
        TABLESPACE EXAMPLE
        NOLOGGING
        STORAGE (
        INITIAL 65536
        NEXT 1048576
        PCTINCREASE 0
        MINEXTENTS 1
        MAXEXTENTS 2147483645
        FREELISTS 1
        FREELIST GROUPS 1
        BUFFER_POOL DEFAULT
    )
    COMPRESS ,
    PARTITION COSTS_1996 VALUES LESS THAN ( TO_DATE(' 1997-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN') )
        PCTFREE 0
        PCTUSED 40
        MAXTRANS 255
        TABLESPACE EXAMPLE
        NOLOGGING
        STORAGE (
        INITIAL 65536
        NEXT 1048576
        PCTINCREASE 0
        MINEXTENTS 1
        MAXEXTENTS 2147483645
        FREELISTS 1
        FREELIST GROUPS 1
        BUFFER_POOL DEFAULT
    )
    COMPRESS ,
    PARTITION COSTS_H1_1997 VALUES LESS THAN ( TO_DATE(' 1997-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN') )
        PCTFREE 0
        PCTUSED 40
        MAXTRANS 255
        TABLESPACE EXAMPLE
        NOLOGGING
        STORAGE (
        INITIAL 65536
        NEXT 1048576
        PCTINCREASE 0
        MINEXTENTS 1
        MAXEXTENTS 2147483645
        FREELISTS 1
        FREELIST GROUPS 1
        BUFFER_POOL DEFAULT
    )
    COMPRESS ,
    PARTITION COSTS_H2_1997 VALUES LESS THAN ( TO_DATE(' 1998-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN') )
        PCTFREE 0
        PCTUSED 40
        MAXTRANS 255
        TABLESPACE EXAMPLE
        NOLOGGING
        STORAGE (
        INITIAL 65536
        NEXT 1048576
        PCTINCREASE 0
        MINEXTENTS 1
        MAXEXTENTS 2147483645
        FREELISTS 1
        FREELIST GROUPS 1
        BUFFER_POOL DEFAULT
    )
    COMPRESS ,
    PARTITION COSTS_Q1_1998 VALUES LESS THAN ( TO_DATE(' 1998-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN') )
        PCTFREE 0
        PCTUSED 40
        MAXTRANS 255
        TABLESPACE EXAMPLE
        NOLOGGING
        STORAGE (
        INITIAL 65536
        NEXT 1048576
        PCTINCREASE 0
        MINEXTENTS 1
        MAXEXTENTS 2147483645
        FREELISTS 1
        FREELIST GROUPS 1
        BUFFER_POOL DEFAULT
    )
    COMPRESS ,
    PARTITION COSTS_Q2_1998 VALUES LESS THAN ( TO_DATE(' 1998-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN') )
        PCTFREE 0
        PCTUSED 40
        MAXTRANS 255
        TABLESPACE EXAMPLE
        NOLOGGING
        STORAGE (
        INITIAL 65536
        NEXT 1048576
        PCTINCREASE 0
        MINEXTENTS 1
        MAXEXTENTS 2147483645
        FREELISTS 1
        FREELIST GROUPS 1
        BUFFER_POOL DEFAULT
    )
    COMPRESS ,
    PARTITION COSTS_Q3_1998 VALUES LESS THAN ( TO_DATE(' 1998-10-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN') )
        PCTFREE 0
        PCTUSED 40
        MAXTRANS 255
        TABLESPACE EXAMPLE
        NOLOGGING
        STORAGE (
        INITIAL 65536
        NEXT 1048576
        PCTINCREASE 0
        MINEXTENTS 1
        MAXEXTENTS 2147483645
        FREELISTS 1
        FREELIST GROUPS 1
        BUFFER_POOL DEFAULT
    )
    COMPRESS ,
    PARTITION COSTS_Q4_1998 VALUES LESS THAN ( TO_DATE(' 1999-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN') )
        PCTFREE 0
        PCTUSED 40
        MAXTRANS 255
        TABLESPACE EXAMPLE
        NOLOGGING
        STORAGE (
        INITIAL 65536
        NEXT 1048576
        PCTINCREASE 0
        MINEXTENTS 1
        MAXEXTENTS 2147483645
        FREELISTS 1
        FREELIST GROUPS 1
        BUFFER_POOL DEFAULT
    )
    COMPRESS ,
    PARTITION COSTS_Q1_1999 VALUES LESS THAN ( TO_DATE(' 1999-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN') )
        PCTFREE 0
        PCTUSED 40
        MAXTRANS 255
        TABLESPACE EXAMPLE
        NOLOGGING
        STORAGE (
        INITIAL 65536
        NEXT 1048576
        PCTINCREASE 0
        MINEXTENTS 1
        MAXEXTENTS 2147483645
        FREELISTS 1
        FREELIST GROUPS 1
        BUFFER_POOL DEFAULT
    )
    COMPRESS ,
    PARTITION COSTS_Q2_1999 VALUES LESS THAN ( TO_DATE(' 1999-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN') )
        PCTFREE 0
        PCTUSED 40
        MAXTRANS 255
        TABLESPACE EXAMPLE
        NOLOGGING
        STORAGE (
        INITIAL 65536
        NEXT 1048576
        PCTINCREASE 0
        MINEXTENTS 1
        MAXEXTENTS 2147483645
        FREELISTS 1
        FREELIST GROUPS 1
        BUFFER_POOL DEFAULT
    )
    COMPRESS ,
    PARTITION COSTS_Q3_1999 VALUES LESS THAN ( TO_DATE(' 1999-10-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN') )
        PCTFREE 0
        PCTUSED 40
        MAXTRANS 255
        TABLESPACE EXAMPLE
        NOLOGGING
        STORAGE (
        INITIAL 65536
        NEXT 1048576
        PCTINCREASE 0
        MINEXTENTS 1
        MAXEXTENTS 2147483645
        FREELISTS 1
        FREELIST GROUPS 1
        BUFFER_POOL DEFAULT
    )
    COMPRESS ,
    PARTITION COSTS_Q4_1999 VALUES LESS THAN ( TO_DATE(' 2000-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN') )
        PCTFREE 0
        PCTUSED 40
        MAXTRANS 255
        TABLESPACE EXAMPLE
        NOLOGGING
        STORAGE (
        INITIAL 65536
        NEXT 1048576
        PCTINCREASE 0
        MINEXTENTS 1
        MAXEXTENTS 2147483645
        FREELISTS 1
        FREELIST GROUPS 1
        BUFFER_POOL DEFAULT
    )
    COMPRESS ,
    PARTITION COSTS_Q1_2000 VALUES LESS THAN ( TO_DATE(' 2000-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN') )
        PCTFREE 0
        PCTUSED 40
        MAXTRANS 255
        TABLESPACE EXAMPLE
        NOLOGGING
        STORAGE (
        INITIAL 65536
        NEXT 1048576
        PCTINCREASE 0
        MINEXTENTS 1
        MAXEXTENTS 2147483645
        FREELISTS 1
        FREELIST GROUPS 1
        BUFFER_POOL DEFAULT
    )
    COMPRESS ,
    PARTITION COSTS_Q2_2000 VALUES LESS THAN ( TO_DATE(' 2000-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN') )
        PCTFREE 0
        PCTUSED 40
        MAXTRANS 255
        TABLESPACE EXAMPLE
        NOLOGGING
        STORAGE (
        INITIAL 65536
        NEXT 1048576
        PCTINCREASE 0
        MINEXTENTS 1
        MAXEXTENTS 2147483645
        FREELISTS 1
        FREELIST GROUPS 1
        BUFFER_POOL DEFAULT
    )
    COMPRESS ,
    PARTITION COSTS_Q3_2000 VALUES LESS THAN ( TO_DATE(' 2000-10-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN') )
        PCTFREE 0
        PCTUSED 40
        MAXTRANS 255
        TABLESPACE EXAMPLE
        NOLOGGING
        STORAGE (
        INITIAL 65536
        NEXT 1048576
        PCTINCREASE 0
        MINEXTENTS 1
        MAXEXTENTS 2147483645
        FREELISTS 1
        FREELIST GROUPS 1
        BUFFER_POOL DEFAULT
    )
    COMPRESS ,
    PARTITION COSTS_Q4_2000 VALUES LESS THAN ( TO_DATE(' 2001-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN') )
        PCTFREE 0
        PCTUSED 40
        MAXTRANS 255
        TABLESPACE EXAMPLE
        NOLOGGING
        STORAGE (
        INITIAL 65536
        NEXT 1048576
        PCTINCREASE 0
        MINEXTENTS 1
        MAXEXTENTS 2147483645
        FREELISTS 1
        FREELIST GROUPS 1
        BUFFER_POOL DEFAULT
    )
    COMPRESS ,
    PARTITION COSTS_Q1_2001 VALUES LESS THAN ( TO_DATE(' 2001-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN') )
        PCTFREE 0
        PCTUSED 40
        MAXTRANS 255
        TABLESPACE EXAMPLE
        NOLOGGING
        STORAGE (
        INITIAL 65536
        NEXT 1048576
        PCTINCREASE 0
        MINEXTENTS 1
        MAXEXTENTS 2147483645
        FREELISTS 1
        FREELIST GROUPS 1
        BUFFER_POOL DEFAULT
    )
    COMPRESS ,
    PARTITION COSTS_Q2_2001 VALUES LESS THAN ( TO_DATE(' 2001-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN') )
        PCTFREE 0
        PCTUSED 40
        MAXTRANS 255
        TABLESPACE EXAMPLE
        NOLOGGING
        STORAGE (
        INITIAL 65536
        NEXT 1048576
        PCTINCREASE 0
        MINEXTENTS 1
        MAXEXTENTS 2147483645
        FREELISTS 1
        FREELIST GROUPS 1
        BUFFER_POOL DEFAULT
    )
    COMPRESS ,
    PARTITION COSTS_Q3_2001 VALUES LESS THAN ( TO_DATE(' 2001-10-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN') )
        PCTFREE 0
        PCTUSED 40
        MAXTRANS 255
        TABLESPACE EXAMPLE
        NOLOGGING
        STORAGE (
        INITIAL 65536
        NEXT 1048576
        PCTINCREASE 0
        MINEXTENTS 1
        MAXEXTENTS 2147483645
        FREELISTS 1
        FREELIST GROUPS 1
        BUFFER_POOL DEFAULT
    )
    COMPRESS ,
    PARTITION COSTS_Q4_2001 VALUES LESS THAN ( TO_DATE(' 2002-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN') )
        PCTFREE 0
        PCTUSED 40
        MAXTRANS 255
        TABLESPACE EXAMPLE
        NOLOGGING
        STORAGE (
        INITIAL 65536
        NEXT 1048576
        PCTINCREASE 0
        MINEXTENTS 1
        MAXEXTENTS 2147483645
        FREELISTS 1
        FREELIST GROUPS 1
        BUFFER_POOL DEFAULT
    )
    COMPRESS ,
    PARTITION COSTS_Q1_2002 VALUES LESS THAN ( TO_DATE(' 2002-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN') )
        PCTFREE 0
        PCTUSED 40
        MAXTRANS 255
        TABLESPACE EXAMPLE
        NOLOGGING
        STORAGE (
        INITIAL 65536
        NEXT 1048576
        PCTINCREASE 0
        MINEXTENTS 1
        MAXEXTENTS 2147483645
        FREELISTS 1
        FREELIST GROUPS 1
        BUFFER_POOL DEFAULT
    )
    COMPRESS ,
    PARTITION COSTS_Q2_2002 VALUES LESS THAN ( TO_DATE(' 2002-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN') )
        PCTFREE 0
        PCTUSED 40
        MAXTRANS 255
        TABLESPACE EXAMPLE
        NOLOGGING
        STORAGE (
        INITIAL 65536
        NEXT 1048576
        PCTINCREASE 0
        MINEXTENTS 1
        MAXEXTENTS 2147483645
        FREELISTS 1
        FREELIST GROUPS 1
        BUFFER_POOL DEFAULT
    )
    COMPRESS ,
    PARTITION COSTS_Q3_2002 VALUES LESS THAN ( TO_DATE(' 2002-10-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN') )
        PCTFREE 0
        PCTUSED 40
        MAXTRANS 255
        TABLESPACE EXAMPLE
        NOLOGGING
        STORAGE (
        INITIAL 65536
        NEXT 1048576
        PCTINCREASE 0
        MINEXTENTS 1
        MAXEXTENTS 2147483645
        FREELISTS 1
        FREELIST GROUPS 1
        BUFFER_POOL DEFAULT
    )
    COMPRESS ,
    PARTITION COSTS_Q4_2002 VALUES LESS THAN ( TO_DATE(' 2003-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN') )
        PCTFREE 0
        PCTUSED 40
        MAXTRANS 255
        TABLESPACE EXAMPLE
        NOLOGGING
        STORAGE (
        INITIAL 65536
        NEXT 1048576
        PCTINCREASE 0
        MINEXTENTS 1
        MAXEXTENTS 2147483645
        FREELISTS 1
        FREELIST GROUPS 1
        BUFFER_POOL DEFAULT
    )
    COMPRESS ,
    PARTITION COSTS_Q1_2003 VALUES LESS THAN ( TO_DATE(' 2003-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN') )
        PCTFREE 0
        PCTUSED 40
        MAXTRANS 255
        TABLESPACE EXAMPLE
        NOLOGGING
        STORAGE (
        INITIAL 65536
        NEXT 1048576
        PCTINCREASE 0
        MINEXTENTS 1
        MAXEXTENTS 2147483645
        FREELISTS 1
        FREELIST GROUPS 1
        BUFFER_POOL DEFAULT
    )
    COMPRESS ,
    PARTITION COSTS_Q2_2003 VALUES LESS THAN ( TO_DATE(' 2003-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN') )
        PCTFREE 0
        PCTUSED 40
        MAXTRANS 255
        TABLESPACE EXAMPLE
        NOLOGGING
        STORAGE (
        INITIAL 65536
        NEXT 1048576
        PCTINCREASE 0
        MINEXTENTS 1
        MAXEXTENTS 2147483645
        FREELISTS 1
        FREELIST GROUPS 1
        BUFFER_POOL DEFAULT
    )
    COMPRESS ,
    PARTITION COSTS_Q3_2003 VALUES LESS THAN ( TO_DATE(' 2003-10-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN') )
        PCTFREE 0
        PCTUSED 40
        MAXTRANS 255
        TABLESPACE EXAMPLE
        NOLOGGING
        STORAGE (
        INITIAL 65536
        NEXT 1048576
        PCTINCREASE 0
        MINEXTENTS 1
        MAXEXTENTS 2147483645
        FREELISTS 1
        FREELIST GROUPS 1
        BUFFER_POOL DEFAULT
    )
    COMPRESS ,
    PARTITION COSTS_Q4_2003 VALUES LESS THAN ( TO_DATE(' 2004-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN') )
        PCTFREE 5
        PCTUSED 40
        MAXTRANS 255
        TABLESPACE EXAMPLE
        NOLOGGING
        STORAGE (
        INITIAL 65536
        NEXT 1048576
        PCTINCREASE 0
        MINEXTENTS 1
        MAXEXTENTS 2147483645
        FREELISTS 1
        FREELIST GROUPS 1
        BUFFER_POOL DEFAULT
    )
    NOCOMPRESS
    )
;


CREATE BITMAP INDEX SH.COSTS_PROD_BIX ON SH.COSTS
    (
     PROD_ID ASC
    )
    LOCAL
    (
    PARTITION COSTS_1995
        PCTFREE 10
        MAXTRANS 255
        STORAGE (
            INITIAL 65536
            NEXT 1048576
            PCTINCREASE 0
            MINEXTENTS 1
            MAXEXTENTS 2147483645
            FREELISTS 1
            FREELIST GROUPS 1
            BUFFER_POOL DEFAULT )
        TABLESPACE EXAMPLE
        NOLOGGING ,
    PARTITION COSTS_1996
        PCTFREE 10
        MAXTRANS 255
        STORAGE (
            INITIAL 65536
            NEXT 1048576
            PCTINCREASE 0
            MINEXTENTS 1
            MAXEXTENTS 2147483645
            FREELISTS 1
            FREELIST GROUPS 1
            BUFFER_POOL DEFAULT )
        TABLESPACE EXAMPLE
        NOLOGGING ,
    PARTITION COSTS_H1_1997
        PCTFREE 10
        MAXTRANS 255
        STORAGE (
            INITIAL 65536
            NEXT 1048576
            PCTINCREASE 0
            MINEXTENTS 1
            MAXEXTENTS 2147483645
            FREELISTS 1
            FREELIST GROUPS 1
            BUFFER_POOL DEFAULT )
        TABLESPACE EXAMPLE
        NOLOGGING ,
    PARTITION COSTS_H2_1997
        PCTFREE 10
        MAXTRANS 255
        STORAGE (
            INITIAL 65536
            NEXT 1048576
            PCTINCREASE 0
            MINEXTENTS 1
            MAXEXTENTS 2147483645
            FREELISTS 1
            FREELIST GROUPS 1
            BUFFER_POOL DEFAULT )
        TABLESPACE EXAMPLE
        NOLOGGING ,
    PARTITION COSTS_Q1_1998
        PCTFREE 10
        MAXTRANS 255
        STORAGE (
            INITIAL 65536
            NEXT 1048576
            PCTINCREASE 0
            MINEXTENTS 1
            MAXEXTENTS 2147483645
            FREELISTS 1
            FREELIST GROUPS 1
            BUFFER_POOL DEFAULT )
        TABLESPACE EXAMPLE
        NOLOGGING ,
    PARTITION COSTS_Q2_1998
        PCTFREE 10
        MAXTRANS 255
        STORAGE (
            INITIAL 65536
            NEXT 1048576
            PCTINCREASE 0
            MINEXTENTS 1
            MAXEXTENTS 2147483645
            FREELISTS 1
            FREELIST GROUPS 1
            BUFFER_POOL DEFAULT )
        TABLESPACE EXAMPLE
        NOLOGGING ,
    PARTITION COSTS_Q3_1998
        PCTFREE 10
        MAXTRANS 255
        STORAGE (
            INITIAL 65536
            NEXT 1048576
            PCTINCREASE 0
            MINEXTENTS 1
            MAXEXTENTS 2147483645
            FREELISTS 1
            FREELIST GROUPS 1
            BUFFER_POOL DEFAULT )
        TABLESPACE EXAMPLE
        NOLOGGING ,
    PARTITION COSTS_Q4_1998
        PCTFREE 10
        MAXTRANS 255
        STORAGE (
            INITIAL 65536
            NEXT 1048576
            PCTINCREASE 0
            MINEXTENTS 1
            MAXEXTENTS 2147483645
            FREELISTS 1
            FREELIST GROUPS 1
            BUFFER_POOL DEFAULT )
        TABLESPACE EXAMPLE
        NOLOGGING ,
    PARTITION COSTS_Q1_1999
        PCTFREE 10
        MAXTRANS 255
        STORAGE (
            INITIAL 65536
            NEXT 1048576
            PCTINCREASE 0
            MINEXTENTS 1
            MAXEXTENTS 2147483645
            FREELISTS 1
            FREELIST GROUPS 1
            BUFFER_POOL DEFAULT )
        TABLESPACE EXAMPLE
        NOLOGGING ,
    PARTITION COSTS_Q2_1999
        PCTFREE 10
        MAXTRANS 255
        STORAGE (
            INITIAL 65536
            NEXT 1048576
            PCTINCREASE 0
            MINEXTENTS 1
            MAXEXTENTS 2147483645
            FREELISTS 1
            FREELIST GROUPS 1
            BUFFER_POOL DEFAULT )
        TABLESPACE EXAMPLE
        NOLOGGING ,
    PARTITION COSTS_Q3_1999
        PCTFREE 10
        MAXTRANS 255
        STORAGE (
            INITIAL 65536
            NEXT 1048576
            PCTINCREASE 0
            MINEXTENTS 1
            MAXEXTENTS 2147483645
            FREELISTS 1
            FREELIST GROUPS 1
            BUFFER_POOL DEFAULT )
        TABLESPACE EXAMPLE
        NOLOGGING ,
    PARTITION COSTS_Q4_1999
        PCTFREE 10
        MAXTRANS 255
        STORAGE (
            INITIAL 65536
            NEXT 1048576
            PCTINCREASE 0
            MINEXTENTS 1
            MAXEXTENTS 2147483645
            FREELISTS 1
            FREELIST GROUPS 1
            BUFFER_POOL DEFAULT )
        TABLESPACE EXAMPLE
        NOLOGGING ,
    PARTITION COSTS_Q1_2000
        PCTFREE 10
        MAXTRANS 255
        STORAGE (
            INITIAL 65536
            NEXT 1048576
            PCTINCREASE 0
            MINEXTENTS 1
            MAXEXTENTS 2147483645
            FREELISTS 1
            FREELIST GROUPS 1
            BUFFER_POOL DEFAULT )
        TABLESPACE EXAMPLE
        NOLOGGING ,
    PARTITION COSTS_Q2_2000
        PCTFREE 10
        MAXTRANS 255
        STORAGE (
            INITIAL 65536
            NEXT 1048576
            PCTINCREASE 0
            MINEXTENTS 1
            MAXEXTENTS 2147483645
            FREELISTS 1
            FREELIST GROUPS 1
            BUFFER_POOL DEFAULT )
        TABLESPACE EXAMPLE
        NOLOGGING ,
    PARTITION COSTS_Q3_2000
        PCTFREE 10
        MAXTRANS 255
        STORAGE (
            INITIAL 65536
            NEXT 1048576
            PCTINCREASE 0
            MINEXTENTS 1
            MAXEXTENTS 2147483645
            FREELISTS 1
            FREELIST GROUPS 1
            BUFFER_POOL DEFAULT )
        TABLESPACE EXAMPLE
        NOLOGGING ,
    PARTITION COSTS_Q4_2000
        PCTFREE 10
        MAXTRANS 255
        STORAGE (
            INITIAL 65536
            NEXT 1048576
            PCTINCREASE 0
            MINEXTENTS 1
            MAXEXTENTS 2147483645
            FREELISTS 1
            FREELIST GROUPS 1
            BUFFER_POOL DEFAULT )
        TABLESPACE EXAMPLE
        NOLOGGING ,
    PARTITION COSTS_Q1_2001
        PCTFREE 10
        MAXTRANS 255
        STORAGE (
            INITIAL 65536
            NEXT 1048576
            PCTINCREASE 0
            MINEXTENTS 1
            MAXEXTENTS 2147483645
            FREELISTS 1
            FREELIST GROUPS 1
            BUFFER_POOL DEFAULT )
        TABLESPACE EXAMPLE
        NOLOGGING ,
    PARTITION COSTS_Q2_2001
        PCTFREE 10
        MAXTRANS 255
        STORAGE (
            INITIAL 65536
            NEXT 1048576
            PCTINCREASE 0
            MINEXTENTS 1
            MAXEXTENTS 2147483645
            FREELISTS 1
            FREELIST GROUPS 1
            BUFFER_POOL DEFAULT )
        TABLESPACE EXAMPLE
        NOLOGGING ,
    PARTITION COSTS_Q3_2001
        PCTFREE 10
        MAXTRANS 255
        STORAGE (
            INITIAL 65536
            NEXT 1048576
            PCTINCREASE 0
            MINEXTENTS 1
            MAXEXTENTS 2147483645
            FREELISTS 1
            FREELIST GROUPS 1
            BUFFER_POOL DEFAULT )
        TABLESPACE EXAMPLE
        NOLOGGING ,
    PARTITION COSTS_Q4_2001
        PCTFREE 10
        MAXTRANS 255
        STORAGE (
            INITIAL 65536
            NEXT 1048576
            PCTINCREASE 0
            MINEXTENTS 1
            MAXEXTENTS 2147483645
            FREELISTS 1
            FREELIST GROUPS 1
            BUFFER_POOL DEFAULT )
        TABLESPACE EXAMPLE
        NOLOGGING ,
    PARTITION COSTS_Q1_2002
        PCTFREE 10
        MAXTRANS 255
        STORAGE (
            INITIAL 65536
            NEXT 1048576
            PCTINCREASE 0
            MINEXTENTS 1
            MAXEXTENTS 2147483645
            FREELISTS 1
            FREELIST GROUPS 1
            BUFFER_POOL DEFAULT )
        TABLESPACE EXAMPLE
        NOLOGGING ,
    PARTITION COSTS_Q2_2002
        PCTFREE 10
        MAXTRANS 255
        STORAGE (
            INITIAL 65536
            NEXT 1048576
            PCTINCREASE 0
            MINEXTENTS 1
            MAXEXTENTS 2147483645
            FREELISTS 1
            FREELIST GROUPS 1
            BUFFER_POOL DEFAULT )
        TABLESPACE EXAMPLE
        NOLOGGING ,
    PARTITION COSTS_Q3_2002
        PCTFREE 10
        MAXTRANS 255
        STORAGE (
            INITIAL 65536
            NEXT 1048576
            PCTINCREASE 0
            MINEXTENTS 1
            MAXEXTENTS 2147483645
            FREELISTS 1
            FREELIST GROUPS 1
            BUFFER_POOL DEFAULT )
        TABLESPACE EXAMPLE
        NOLOGGING ,
    PARTITION COSTS_Q4_2002
        PCTFREE 10
        MAXTRANS 255
        STORAGE (
            INITIAL 65536
            NEXT 1048576
            PCTINCREASE 0
            MINEXTENTS 1
            MAXEXTENTS 2147483645
            FREELISTS 1
            FREELIST GROUPS 1
            BUFFER_POOL DEFAULT )
        TABLESPACE EXAMPLE
        NOLOGGING ,
    PARTITION COSTS_Q1_2003
        PCTFREE 10
        MAXTRANS 255
        STORAGE (
            INITIAL 65536
            NEXT 1048576
            PCTINCREASE 0
            MINEXTENTS 1
            MAXEXTENTS 2147483645
            FREELISTS 1
            FREELIST GROUPS 1
            BUFFER_POOL DEFAULT )
        TABLESPACE EXAMPLE
        NOLOGGING ,
    PARTITION COSTS_Q2_2003
        PCTFREE 10
        MAXTRANS 255
        STORAGE (
            INITIAL 65536
            NEXT 1048576
            PCTINCREASE 0
            MINEXTENTS 1
            MAXEXTENTS 2147483645
            FREELISTS 1
            FREELIST GROUPS 1
            BUFFER_POOL DEFAULT )
        TABLESPACE EXAMPLE
        NOLOGGING ,
    PARTITION COSTS_Q3_2003
        PCTFREE 10
        MAXTRANS 255
        STORAGE (
            INITIAL 65536
            NEXT 1048576
            PCTINCREASE 0
            MINEXTENTS 1
            MAXEXTENTS 2147483645
            FREELISTS 1
            FREELIST GROUPS 1
            BUFFER_POOL DEFAULT )
        TABLESPACE EXAMPLE
        NOLOGGING ,
    PARTITION COSTS_Q4_2003
        PCTFREE 10
        MAXTRANS 255
        STORAGE (
            INITIAL 65536
            NEXT 1048576
            PCTINCREASE 0
            MINEXTENTS 1
            MAXEXTENTS 2147483645
            FREELISTS 1
            FREELIST GROUPS 1
            BUFFER_POOL DEFAULT )
        TABLESPACE EXAMPLE
        NOLOGGING
    )
    PCTFREE 10
    MAXTRANS 255
    STORAGE (
        PCTINCREASE 0
        MINEXTENTS 1
        MAXEXTENTS UNLIMITED
        FREELISTS 1
        FREELIST GROUPS 1
        BUFFER_POOL DEFAULT )
    NOLOGGING
;
CREATE BITMAP INDEX SH.COSTS_TIME_BIX ON SH.COSTS
    (
     TIME_ID ASC
    )
    LOCAL
    (
    PARTITION COSTS_1995
        PCTFREE 10
        MAXTRANS 255
        STORAGE (
            INITIAL 65536
            NEXT 1048576
            PCTINCREASE 0
            MINEXTENTS 1
            MAXEXTENTS 2147483645
            FREELISTS 1
            FREELIST GROUPS 1
            BUFFER_POOL DEFAULT )
        TABLESPACE EXAMPLE
        NOLOGGING ,
    PARTITION COSTS_1996
        PCTFREE 10
        MAXTRANS 255
        STORAGE (
            INITIAL 65536
            NEXT 1048576
            PCTINCREASE 0
            MINEXTENTS 1
            MAXEXTENTS 2147483645
            FREELISTS 1
            FREELIST GROUPS 1
            BUFFER_POOL DEFAULT )
        TABLESPACE EXAMPLE
        NOLOGGING ,
    PARTITION COSTS_H1_1997
        PCTFREE 10
        MAXTRANS 255
        STORAGE (
            INITIAL 65536
            NEXT 1048576
            PCTINCREASE 0
            MINEXTENTS 1
            MAXEXTENTS 2147483645
            FREELISTS 1
            FREELIST GROUPS 1
            BUFFER_POOL DEFAULT )
        TABLESPACE EXAMPLE
        NOLOGGING ,
    PARTITION COSTS_H2_1997
        PCTFREE 10
        MAXTRANS 255
        STORAGE (
            INITIAL 65536
            NEXT 1048576
            PCTINCREASE 0
            MINEXTENTS 1
            MAXEXTENTS 2147483645
            FREELISTS 1
            FREELIST GROUPS 1
            BUFFER_POOL DEFAULT )
        TABLESPACE EXAMPLE
        NOLOGGING ,
    PARTITION COSTS_Q1_1998
        PCTFREE 10
        MAXTRANS 255
        STORAGE (
            INITIAL 65536
            NEXT 1048576
            PCTINCREASE 0
            MINEXTENTS 1
            MAXEXTENTS 2147483645
            FREELISTS 1
            FREELIST GROUPS 1
            BUFFER_POOL DEFAULT )
        TABLESPACE EXAMPLE
        NOLOGGING ,
    PARTITION COSTS_Q2_1998
        PCTFREE 10
        MAXTRANS 255
        STORAGE (
            INITIAL 65536
            NEXT 1048576
            PCTINCREASE 0
            MINEXTENTS 1
            MAXEXTENTS 2147483645
            FREELISTS 1
            FREELIST GROUPS 1
            BUFFER_POOL DEFAULT )
        TABLESPACE EXAMPLE
        NOLOGGING ,
    PARTITION COSTS_Q3_1998
        PCTFREE 10
        MAXTRANS 255
        STORAGE (
            INITIAL 65536
            NEXT 1048576
            PCTINCREASE 0
            MINEXTENTS 1
            MAXEXTENTS 2147483645
            FREELISTS 1
            FREELIST GROUPS 1
            BUFFER_POOL DEFAULT )
        TABLESPACE EXAMPLE
        NOLOGGING ,
    PARTITION COSTS_Q4_1998
        PCTFREE 10
        MAXTRANS 255
        STORAGE (
            INITIAL 65536
            NEXT 1048576
            PCTINCREASE 0
            MINEXTENTS 1
            MAXEXTENTS 2147483645
            FREELISTS 1
            FREELIST GROUPS 1
            BUFFER_POOL DEFAULT )
        TABLESPACE EXAMPLE
        NOLOGGING ,
    PARTITION COSTS_Q1_1999
        PCTFREE 10
        MAXTRANS 255
        STORAGE (
            INITIAL 65536
            NEXT 1048576
            PCTINCREASE 0
            MINEXTENTS 1
            MAXEXTENTS 2147483645
            FREELISTS 1
            FREELIST GROUPS 1
            BUFFER_POOL DEFAULT )
        TABLESPACE EXAMPLE
        NOLOGGING ,
    PARTITION COSTS_Q2_1999
        PCTFREE 10
        MAXTRANS 255
        STORAGE (
            INITIAL 65536
            NEXT 1048576
            PCTINCREASE 0
            MINEXTENTS 1
            MAXEXTENTS 2147483645
            FREELISTS 1
            FREELIST GROUPS 1
            BUFFER_POOL DEFAULT )
        TABLESPACE EXAMPLE
        NOLOGGING ,
    PARTITION COSTS_Q3_1999
        PCTFREE 10
        MAXTRANS 255
        STORAGE (
            INITIAL 65536
            NEXT 1048576
            PCTINCREASE 0
            MINEXTENTS 1
            MAXEXTENTS 2147483645
            FREELISTS 1
            FREELIST GROUPS 1
            BUFFER_POOL DEFAULT )
        TABLESPACE EXAMPLE
        NOLOGGING ,
    PARTITION COSTS_Q4_1999
        PCTFREE 10
        MAXTRANS 255
        STORAGE (
            INITIAL 65536
            NEXT 1048576
            PCTINCREASE 0
            MINEXTENTS 1
            MAXEXTENTS 2147483645
            FREELISTS 1
            FREELIST GROUPS 1
            BUFFER_POOL DEFAULT )
        TABLESPACE EXAMPLE
        NOLOGGING ,
    PARTITION COSTS_Q1_2000
        PCTFREE 10
        MAXTRANS 255
        STORAGE (
            INITIAL 65536
            NEXT 1048576
            PCTINCREASE 0
            MINEXTENTS 1
            MAXEXTENTS 2147483645
            FREELISTS 1
            FREELIST GROUPS 1
            BUFFER_POOL DEFAULT )
        TABLESPACE EXAMPLE
        NOLOGGING ,
    PARTITION COSTS_Q2_2000
        PCTFREE 10
        MAXTRANS 255
        STORAGE (
            INITIAL 65536
            NEXT 1048576
            PCTINCREASE 0
            MINEXTENTS 1
            MAXEXTENTS 2147483645
            FREELISTS 1
            FREELIST GROUPS 1
            BUFFER_POOL DEFAULT )
        TABLESPACE EXAMPLE
        NOLOGGING ,
    PARTITION COSTS_Q3_2000
        PCTFREE 10
        MAXTRANS 255
        STORAGE (
            INITIAL 65536
            NEXT 1048576
            PCTINCREASE 0
            MINEXTENTS 1
            MAXEXTENTS 2147483645
            FREELISTS 1
            FREELIST GROUPS 1
            BUFFER_POOL DEFAULT )
        TABLESPACE EXAMPLE
        NOLOGGING ,
    PARTITION COSTS_Q4_2000
        PCTFREE 10
        MAXTRANS 255
        STORAGE (
            INITIAL 65536
            NEXT 1048576
            PCTINCREASE 0
            MINEXTENTS 1
            MAXEXTENTS 2147483645
            FREELISTS 1
            FREELIST GROUPS 1
            BUFFER_POOL DEFAULT )
        TABLESPACE EXAMPLE
        NOLOGGING ,
    PARTITION COSTS_Q1_2001
        PCTFREE 10
        MAXTRANS 255
        STORAGE (
            INITIAL 65536
            NEXT 1048576
            PCTINCREASE 0
            MINEXTENTS 1
            MAXEXTENTS 2147483645
            FREELISTS 1
            FREELIST GROUPS 1
            BUFFER_POOL DEFAULT )
        TABLESPACE EXAMPLE
        NOLOGGING ,
    PARTITION COSTS_Q2_2001
        PCTFREE 10
        MAXTRANS 255
        STORAGE (
            INITIAL 65536
            NEXT 1048576
            PCTINCREASE 0
            MINEXTENTS 1
            MAXEXTENTS 2147483645
            FREELISTS 1
            FREELIST GROUPS 1
            BUFFER_POOL DEFAULT )
        TABLESPACE EXAMPLE
        NOLOGGING ,
    PARTITION COSTS_Q3_2001
        PCTFREE 10
        MAXTRANS 255
        STORAGE (
            INITIAL 65536
            NEXT 1048576
            PCTINCREASE 0
            MINEXTENTS 1
            MAXEXTENTS 2147483645
            FREELISTS 1
            FREELIST GROUPS 1
            BUFFER_POOL DEFAULT )
        TABLESPACE EXAMPLE
        NOLOGGING ,
    PARTITION COSTS_Q4_2001
        PCTFREE 10
        MAXTRANS 255
        STORAGE (
            INITIAL 65536
            NEXT 1048576
            PCTINCREASE 0
            MINEXTENTS 1
            MAXEXTENTS 2147483645
            FREELISTS 1
            FREELIST GROUPS 1
            BUFFER_POOL DEFAULT )
        TABLESPACE EXAMPLE
        NOLOGGING ,
    PARTITION COSTS_Q1_2002
        PCTFREE 10
        MAXTRANS 255
        STORAGE (
            INITIAL 65536
            NEXT 1048576
            PCTINCREASE 0
            MINEXTENTS 1
            MAXEXTENTS 2147483645
            FREELISTS 1
            FREELIST GROUPS 1
            BUFFER_POOL DEFAULT )
        TABLESPACE EXAMPLE
        NOLOGGING ,
    PARTITION COSTS_Q2_2002
        PCTFREE 10
        MAXTRANS 255
        STORAGE (
            INITIAL 65536
            NEXT 1048576
            PCTINCREASE 0
            MINEXTENTS 1
            MAXEXTENTS 2147483645
            FREELISTS 1
            FREELIST GROUPS 1
            BUFFER_POOL DEFAULT )
        TABLESPACE EXAMPLE
        NOLOGGING ,
    PARTITION COSTS_Q3_2002
        PCTFREE 10
        MAXTRANS 255
        STORAGE (
            INITIAL 65536
            NEXT 1048576
            PCTINCREASE 0
            MINEXTENTS 1
            MAXEXTENTS 2147483645
            FREELISTS 1
            FREELIST GROUPS 1
            BUFFER_POOL DEFAULT )
        TABLESPACE EXAMPLE
        NOLOGGING ,
    PARTITION COSTS_Q4_2002
        PCTFREE 10
        MAXTRANS 255
        STORAGE (
            INITIAL 65536
            NEXT 1048576
            PCTINCREASE 0
            MINEXTENTS 1
            MAXEXTENTS 2147483645
            FREELISTS 1
            FREELIST GROUPS 1
            BUFFER_POOL DEFAULT )
        TABLESPACE EXAMPLE
        NOLOGGING ,
    PARTITION COSTS_Q1_2003
        PCTFREE 10
        MAXTRANS 255
        STORAGE (
            INITIAL 65536
            NEXT 1048576
            PCTINCREASE 0
            MINEXTENTS 1
            MAXEXTENTS 2147483645
            FREELISTS 1
            FREELIST GROUPS 1
            BUFFER_POOL DEFAULT )
        TABLESPACE EXAMPLE
        NOLOGGING ,
    PARTITION COSTS_Q2_2003
        PCTFREE 10
        MAXTRANS 255
        STORAGE (
            INITIAL 65536
            NEXT 1048576
            PCTINCREASE 0
            MINEXTENTS 1
            MAXEXTENTS 2147483645
            FREELISTS 1
            FREELIST GROUPS 1
            BUFFER_POOL DEFAULT )
        TABLESPACE EXAMPLE
        NOLOGGING ,
    PARTITION COSTS_Q3_2003
        PCTFREE 10
        MAXTRANS 255
        STORAGE (
            INITIAL 65536
            NEXT 1048576
            PCTINCREASE 0
            MINEXTENTS 1
            MAXEXTENTS 2147483645
            FREELISTS 1
            FREELIST GROUPS 1
            BUFFER_POOL DEFAULT )
        TABLESPACE EXAMPLE
        NOLOGGING ,
    PARTITION COSTS_Q4_2003
        PCTFREE 10
        MAXTRANS 255
        STORAGE (
            INITIAL 65536
            NEXT 1048576
            PCTINCREASE 0
            MINEXTENTS 1
            MAXEXTENTS 2147483645
            FREELISTS 1
            FREELIST GROUPS 1
            BUFFER_POOL DEFAULT )
        TABLESPACE EXAMPLE
        NOLOGGING
    )
    PCTFREE 10
    MAXTRANS 255
    STORAGE (
        PCTINCREASE 0
        MINEXTENTS 1
        MAXEXTENTS UNLIMITED
        FREELISTS 1
        FREELIST GROUPS 1
        BUFFER_POOL DEFAULT )
    NOLOGGING
;


CREATE TABLE SH.COUNTRIES
    (
     COUNTRY_ID NUMBER  NOT NULL ,
     COUNTRY_ISO_CODE CHAR (2 BYTE)  NOT NULL ,
     COUNTRY_NAME VARCHAR2 (40 BYTE)  NOT NULL ,
     COUNTRY_SUBREGION VARCHAR2 (30 BYTE)  NOT NULL ,
     COUNTRY_SUBREGION_ID NUMBER  NOT NULL ,
     COUNTRY_REGION VARCHAR2 (20 BYTE)  NOT NULL ,
     COUNTRY_REGION_ID NUMBER  NOT NULL ,
     COUNTRY_TOTAL VARCHAR2 (11 BYTE)  NOT NULL ,
     COUNTRY_TOTAL_ID NUMBER  NOT NULL ,
     COUNTRY_NAME_HIST VARCHAR2 (40 BYTE)
    )
        PCTFREE 10
        PCTUSED 40
        MAXTRANS 255
        TABLESPACE EXAMPLE
        NOLOGGING
        STORAGE (
        INITIAL 65536
        NEXT 1048576
        PCTINCREASE 0
        MINEXTENTS 1
        MAXEXTENTS 2147483645
        FREELISTS 1
        FREELIST GROUPS 1
        BUFFER_POOL DEFAULT
    )
;



COMMENT ON TABLE SH.COUNTRIES IS 'country dimension table (snowflake)'
;

COMMENT ON COLUMN SH.COUNTRIES.COUNTRY_ID IS 'primary key'
;

COMMENT ON COLUMN SH.COUNTRIES.COUNTRY_NAME IS 'country name'
;

COMMENT ON COLUMN SH.COUNTRIES.COUNTRY_SUBREGION IS 'e.g. Western Europe, to allow hierarchies'
;

COMMENT ON COLUMN SH.COUNTRIES.COUNTRY_REGION IS 'e.g. Europe, Asia'
;

ALTER TABLE SH.COUNTRIES
    ADD CONSTRAINT COUNTRIES_PK PRIMARY KEY ( COUNTRY_ID ) NOVALIDATE ;


CREATE TABLE SH.CUSTOMERS
    (
     CUST_ID NUMBER  NOT NULL ,
     CUST_FIRST_NAME VARCHAR2 (20 BYTE)  NOT NULL ,
     CUST_LAST_NAME VARCHAR2 (40 BYTE)  NOT NULL ,
     CUST_GENDER CHAR (1 BYTE)  NOT NULL ,
     CUST_YEAR_OF_BIRTH NUMBER (4)  NOT NULL ,
     CUST_MARITAL_STATUS VARCHAR2 (20 BYTE) ,
     CUST_STREET_ADDRESS VARCHAR2 (40 BYTE)  NOT NULL ,
     CUST_POSTAL_CODE VARCHAR2 (10 BYTE)  NOT NULL ,
     CUST_CITY VARCHAR2 (30 BYTE)  NOT NULL ,
     CUST_CITY_ID NUMBER  NOT NULL ,
     CUST_STATE_PROVINCE VARCHAR2 (40 BYTE)  NOT NULL ,
     CUST_STATE_PROVINCE_ID NUMBER  NOT NULL ,
     COUNTRY_ID NUMBER  NOT NULL ,
     CUST_MAIN_PHONE_NUMBER VARCHAR2 (25 BYTE)  NOT NULL ,
     CUST_INCOME_LEVEL VARCHAR2 (30 BYTE) ,
     CUST_CREDIT_LIMIT NUMBER ,
     CUST_EMAIL VARCHAR2 (30 BYTE) ,
     CUST_TOTAL VARCHAR2 (14 BYTE)  NOT NULL ,
     CUST_TOTAL_ID NUMBER  NOT NULL ,
     CUST_SRC_ID NUMBER ,
     CUST_EFF_FROM DATE ,
     CUST_EFF_TO DATE ,
     CUST_VALID VARCHAR2 (1 BYTE)
    )
        PCTFREE 10
        PCTUSED 40
        MAXTRANS 255
        TABLESPACE EXAMPLE
        NOLOGGING
        STORAGE (
        INITIAL 65536
        NEXT 1048576
        PCTINCREASE 0
        MINEXTENTS 1
        MAXEXTENTS 2147483645
        FREELISTS 1
        FREELIST GROUPS 1
        BUFFER_POOL DEFAULT
    )
;



COMMENT ON TABLE SH.CUSTOMERS IS 'dimension table'
;

COMMENT ON COLUMN SH.CUSTOMERS.CUST_ID IS 'primary key'
;

COMMENT ON COLUMN SH.CUSTOMERS.CUST_FIRST_NAME IS 'first name of the customer'
;

COMMENT ON COLUMN SH.CUSTOMERS.CUST_LAST_NAME IS 'last name of the customer'
;

COMMENT ON COLUMN SH.CUSTOMERS.CUST_GENDER IS 'gender; low cardinality attribute'
;

COMMENT ON COLUMN SH.CUSTOMERS.CUST_YEAR_OF_BIRTH IS 'customer year of birth'
;

COMMENT ON COLUMN SH.CUSTOMERS.CUST_MARITAL_STATUS IS 'customer marital status; low cardinality attribute'
;

COMMENT ON COLUMN SH.CUSTOMERS.CUST_STREET_ADDRESS IS 'customer street address'
;

COMMENT ON COLUMN SH.CUSTOMERS.CUST_POSTAL_CODE IS 'postal code of the customer'
;

COMMENT ON COLUMN SH.CUSTOMERS.CUST_CITY IS 'city where the customer lives'
;

COMMENT ON COLUMN SH.CUSTOMERS.CUST_STATE_PROVINCE IS 'customer geography: state or province'
;

COMMENT ON COLUMN SH.CUSTOMERS.COUNTRY_ID IS 'foreign key to the countries table (snowflake)'
;

COMMENT ON COLUMN SH.CUSTOMERS.CUST_MAIN_PHONE_NUMBER IS 'customer main phone number'
;

COMMENT ON COLUMN SH.CUSTOMERS.CUST_INCOME_LEVEL IS 'customer income level'
;

COMMENT ON COLUMN SH.CUSTOMERS.CUST_CREDIT_LIMIT IS 'customer credit limit'
;

COMMENT ON COLUMN SH.CUSTOMERS.CUST_EMAIL IS 'customer email id'
;
CREATE BITMAP INDEX SH.CUSTOMERS_GENDER_BIX ON SH.CUSTOMERS
    (
     CUST_GENDER ASC
    )
    TABLESPACE EXAMPLE
    PCTFREE 10
    MAXTRANS 255
    STORAGE (
        INITIAL 65536
        NEXT 1048576
        PCTINCREASE 0
        MINEXTENTS 1
        MAXEXTENTS 2147483645
        FREELISTS 1
        FREELIST GROUPS 1
        BUFFER_POOL DEFAULT )
    NOLOGGING
;
CREATE BITMAP INDEX SH.CUSTOMERS_MARITAL_BIX ON SH.CUSTOMERS
    (
     CUST_MARITAL_STATUS ASC
    )
    TABLESPACE EXAMPLE
    PCTFREE 10
    MAXTRANS 255
    STORAGE (
        INITIAL 65536
        NEXT 1048576
        PCTINCREASE 0
        MINEXTENTS 1
        MAXEXTENTS 2147483645
        FREELISTS 1
        FREELIST GROUPS 1
        BUFFER_POOL DEFAULT )
    NOLOGGING
;
CREATE BITMAP INDEX SH.CUSTOMERS_YOB_BIX ON SH.CUSTOMERS
    (
     CUST_YEAR_OF_BIRTH ASC
    )
    TABLESPACE EXAMPLE
    PCTFREE 10
    MAXTRANS 255
    STORAGE (
        INITIAL 65536
        NEXT 1048576
        PCTINCREASE 0
        MINEXTENTS 1
        MAXEXTENTS 2147483645
        FREELISTS 1
        FREELIST GROUPS 1
        BUFFER_POOL DEFAULT )
    NOLOGGING
;

ALTER TABLE SH.CUSTOMERS
    ADD CONSTRAINT CUSTOMERS_PK PRIMARY KEY ( CUST_ID ) NOVALIDATE ;


CREATE TABLE SH.DIMENSION_EXCEPTIONS
    (
     STATEMENT_ID VARCHAR2 (30 BYTE) ,
     OWNER VARCHAR2 (30 BYTE)  NOT NULL ,
     TABLE_NAME VARCHAR2 (30 BYTE)  NOT NULL ,
     DIMENSION_NAME VARCHAR2 (30 BYTE)  NOT NULL ,
     RELATIONSHIP VARCHAR2 (11 BYTE)  NOT NULL ,
     BAD_ROWID ROWID  NOT NULL
    )
        PCTFREE 10
        PCTUSED 40
        MAXTRANS 255
        TABLESPACE USERS
        LOGGING
        STORAGE (
        PCTINCREASE 0
        MINEXTENTS 1
        MAXEXTENTS UNLIMITED
        FREELISTS 1
        FREELIST GROUPS 1
        BUFFER_POOL DEFAULT
    )
;




CREATE TABLE SH.PRODUCTS
    (
     PROD_ID NUMBER (6)  NOT NULL ,
     PROD_NAME VARCHAR2 (50 BYTE)  NOT NULL ,
     PROD_DESC VARCHAR2 (4000 BYTE)  NOT NULL ,
     PROD_SUBCATEGORY VARCHAR2 (50 BYTE)  NOT NULL ,
     PROD_SUBCATEGORY_ID NUMBER  NOT NULL ,
     PROD_SUBCATEGORY_DESC VARCHAR2 (2000 BYTE)  NOT NULL ,
     PROD_CATEGORY VARCHAR2 (50 BYTE)  NOT NULL ,
     PROD_CATEGORY_ID NUMBER  NOT NULL ,
     PROD_CATEGORY_DESC VARCHAR2 (2000 BYTE)  NOT NULL ,
     PROD_WEIGHT_CLASS NUMBER (3)  NOT NULL ,
     PROD_UNIT_OF_MEASURE VARCHAR2 (20 BYTE) ,
     PROD_PACK_SIZE VARCHAR2 (30 BYTE)  NOT NULL ,
     SUPPLIER_ID NUMBER (6)  NOT NULL ,
     PROD_STATUS VARCHAR2 (20 BYTE)  NOT NULL ,
     PROD_LIST_PRICE NUMBER (8,2)  NOT NULL ,
     PROD_MIN_PRICE NUMBER (8,2)  NOT NULL ,
     PROD_TOTAL VARCHAR2 (13 BYTE)  NOT NULL ,
     PROD_TOTAL_ID NUMBER  NOT NULL ,
     PROD_SRC_ID NUMBER ,
     PROD_EFF_FROM DATE ,
     PROD_EFF_TO DATE ,
     PROD_VALID VARCHAR2 (1 BYTE)
    )
        PCTFREE 10
        PCTUSED 40
        MAXTRANS 255
        TABLESPACE EXAMPLE
        NOLOGGING
        STORAGE (
        INITIAL 65536
        NEXT 1048576
        PCTINCREASE 0
        MINEXTENTS 1
        MAXEXTENTS 2147483645
        FREELISTS 1
        FREELIST GROUPS 1
        BUFFER_POOL DEFAULT
    )
;



COMMENT ON TABLE SH.PRODUCTS IS 'dimension table'
;

COMMENT ON COLUMN SH.PRODUCTS.PROD_ID IS 'primary key'
;

COMMENT ON COLUMN SH.PRODUCTS.PROD_NAME IS 'product name'
;

COMMENT ON COLUMN SH.PRODUCTS.PROD_DESC IS 'product description'
;

COMMENT ON COLUMN SH.PRODUCTS.PROD_SUBCATEGORY IS 'product subcategory'
;

COMMENT ON COLUMN SH.PRODUCTS.PROD_SUBCATEGORY_DESC IS 'product subcategory description'
;

COMMENT ON COLUMN SH.PRODUCTS.PROD_CATEGORY IS 'product category'
;

COMMENT ON COLUMN SH.PRODUCTS.PROD_CATEGORY_DESC IS 'product category description'
;

COMMENT ON COLUMN SH.PRODUCTS.PROD_WEIGHT_CLASS IS 'product weight class'
;

COMMENT ON COLUMN SH.PRODUCTS.PROD_UNIT_OF_MEASURE IS 'product unit of measure'
;

COMMENT ON COLUMN SH.PRODUCTS.PROD_PACK_SIZE IS 'product package size'
;

COMMENT ON COLUMN SH.PRODUCTS.SUPPLIER_ID IS 'this column'
;

COMMENT ON COLUMN SH.PRODUCTS.PROD_STATUS IS 'product status'
;

COMMENT ON COLUMN SH.PRODUCTS.PROD_LIST_PRICE IS 'product list price'
;

COMMENT ON COLUMN SH.PRODUCTS.PROD_MIN_PRICE IS 'product minimum price'
;
CREATE BITMAP INDEX SH.PRODUCTS_PROD_STATUS_BIX ON SH.PRODUCTS
    (
     PROD_STATUS ASC
    )
    TABLESPACE EXAMPLE
    PCTFREE 10
    MAXTRANS 255
    STORAGE (
        INITIAL 65536
        NEXT 1048576
        PCTINCREASE 0
        MINEXTENTS 1
        MAXEXTENTS 2147483645
        FREELISTS 1
        FREELIST GROUPS 1
        BUFFER_POOL DEFAULT )
    NOLOGGING
;
CREATE INDEX SH.PRODUCTS_PROD_SUBCAT_IX ON SH.PRODUCTS
    (
     PROD_SUBCATEGORY ASC
    )
    TABLESPACE EXAMPLE
    PCTFREE 10
    MAXTRANS 255
    STORAGE (
        INITIAL 65536
        NEXT 1048576
        PCTINCREASE 0
        MINEXTENTS 1
        MAXEXTENTS 2147483645
        FREELISTS 1
        FREELIST GROUPS 1
        BUFFER_POOL DEFAULT )
    NOLOGGING
;
CREATE INDEX SH.PRODUCTS_PROD_CAT_IX ON SH.PRODUCTS
    (
     PROD_CATEGORY ASC
    )
    TABLESPACE EXAMPLE
    PCTFREE 10
    MAXTRANS 255
    STORAGE (
        INITIAL 65536
        NEXT 1048576
        PCTINCREASE 0
        MINEXTENTS 1
        MAXEXTENTS 2147483645
        FREELISTS 1
        FREELIST GROUPS 1
        BUFFER_POOL DEFAULT )
    NOLOGGING
;

ALTER TABLE SH.PRODUCTS
    ADD CONSTRAINT PRODUCTS_PK PRIMARY KEY ( PROD_ID ) NOVALIDATE ;


CREATE TABLE SH.PROMOTIONS
    (
     PROMO_ID NUMBER (6)  NOT NULL ,
     PROMO_NAME VARCHAR2 (30 BYTE)  NOT NULL ,
     PROMO_SUBCATEGORY VARCHAR2 (30 BYTE)  NOT NULL ,
     PROMO_SUBCATEGORY_ID NUMBER  NOT NULL ,
     PROMO_CATEGORY VARCHAR2 (30 BYTE)  NOT NULL ,
     PROMO_CATEGORY_ID NUMBER  NOT NULL ,
     PROMO_COST NUMBER (10,2)  NOT NULL ,
     PROMO_BEGIN_DATE DATE  NOT NULL ,
     PROMO_END_DATE DATE  NOT NULL ,
     PROMO_TOTAL VARCHAR2 (15 BYTE)  NOT NULL ,
     PROMO_TOTAL_ID NUMBER  NOT NULL
    )
        PCTFREE 10
        PCTUSED 40
        MAXTRANS 255
        TABLESPACE EXAMPLE
        NOLOGGING
        STORAGE (
        INITIAL 65536
        NEXT 1048576
        PCTINCREASE 0
        MINEXTENTS 1
        MAXEXTENTS 2147483645
        FREELISTS 1
        FREELIST GROUPS 1
        BUFFER_POOL DEFAULT
    )
;



COMMENT ON TABLE SH.PROMOTIONS IS 'dimension table without a PK-FK relationship with the facts table, to show outer join functionality'
;

COMMENT ON COLUMN SH.PROMOTIONS.PROMO_ID IS 'primary key column'
;

COMMENT ON COLUMN SH.PROMOTIONS.PROMO_NAME IS 'promotion description'
;

COMMENT ON COLUMN SH.PROMOTIONS.PROMO_SUBCATEGORY IS 'enables to investigate promotion hierarchies'
;

COMMENT ON COLUMN SH.PROMOTIONS.PROMO_CATEGORY IS 'promotion category'
;

COMMENT ON COLUMN SH.PROMOTIONS.PROMO_COST IS 'promotion cost, to do promotion effect calculations'
;

COMMENT ON COLUMN SH.PROMOTIONS.PROMO_BEGIN_DATE IS 'promotion begin day'
;

COMMENT ON COLUMN SH.PROMOTIONS.PROMO_END_DATE IS 'promotion end day'
;

ALTER TABLE SH.PROMOTIONS
    ADD CONSTRAINT PROMO_PK PRIMARY KEY ( PROMO_ID ) NOVALIDATE ;


CREATE TABLE SH.SALES
    (
     PROD_ID NUMBER (6)  NOT NULL ,
     CUST_ID NUMBER  NOT NULL ,
     TIME_ID DATE  NOT NULL ,
     CHANNEL_ID NUMBER  NOT NULL ,
     PROMO_ID NUMBER (6)  NOT NULL ,
     QUANTITY_SOLD NUMBER (10,2)  NOT NULL ,
     AMOUNT_SOLD NUMBER (10,2)  NOT NULL
    )
        PCTFREE 5
        PCTUSED 40
        MAXTRANS 255
        TABLESPACE EXAMPLE
        NOLOGGING
        STORAGE (
        PCTINCREASE 0
        MINEXTENTS 1
        MAXEXTENTS UNLIMITED
        FREELISTS 1
        FREELIST GROUPS 1
        BUFFER_POOL DEFAULT
    )
    DISABLE ROW MOVEMENT
    PARTITION BY RANGE ( TIME_ID )
    (
    PARTITION SALES_1995 VALUES LESS THAN ( TO_DATE(' 1996-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN') )
        PCTFREE 0
        PCTUSED 40
        MAXTRANS 255
        TABLESPACE EXAMPLE
        NOLOGGING
        STORAGE (
        INITIAL 65536
        NEXT 1048576
        PCTINCREASE 0
        MINEXTENTS 1
        MAXEXTENTS 2147483645
        FREELISTS 1
        FREELIST GROUPS 1
        BUFFER_POOL DEFAULT
    )
    COMPRESS ,
    PARTITION SALES_1996 VALUES LESS THAN ( TO_DATE(' 1997-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN') )
        PCTFREE 0
        PCTUSED 40
        MAXTRANS 255
        TABLESPACE EXAMPLE
        NOLOGGING
        STORAGE (
        INITIAL 65536
        NEXT 1048576
        PCTINCREASE 0
        MINEXTENTS 1
        MAXEXTENTS 2147483645
        FREELISTS 1
        FREELIST GROUPS 1
        BUFFER_POOL DEFAULT
    )
    COMPRESS ,
    PARTITION SALES_H1_1997 VALUES LESS THAN ( TO_DATE(' 1997-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN') )
        PCTFREE 0
        PCTUSED 40
        MAXTRANS 255
        TABLESPACE EXAMPLE
        NOLOGGING
        STORAGE (
        INITIAL 65536
        NEXT 1048576
        PCTINCREASE 0
        MINEXTENTS 1
        MAXEXTENTS 2147483645
        FREELISTS 1
        FREELIST GROUPS 1
        BUFFER_POOL DEFAULT
    )
    COMPRESS ,
    PARTITION SALES_H2_1997 VALUES LESS THAN ( TO_DATE(' 1998-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN') )
        PCTFREE 0
        PCTUSED 40
        MAXTRANS 255
        TABLESPACE EXAMPLE
        NOLOGGING
        STORAGE (
        INITIAL 65536
        NEXT 1048576
        PCTINCREASE 0
        MINEXTENTS 1
        MAXEXTENTS 2147483645
        FREELISTS 1
        FREELIST GROUPS 1
        BUFFER_POOL DEFAULT
    )
    COMPRESS ,
    PARTITION SALES_Q1_1998 VALUES LESS THAN ( TO_DATE(' 1998-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN') )
        PCTFREE 0
        PCTUSED 40
        MAXTRANS 255
        TABLESPACE EXAMPLE
        NOLOGGING
        STORAGE (
        INITIAL 65536
        NEXT 1048576
        PCTINCREASE 0
        MINEXTENTS 1
        MAXEXTENTS 2147483645
        FREELISTS 1
        FREELIST GROUPS 1
        BUFFER_POOL DEFAULT
    )
    COMPRESS ,
    PARTITION SALES_Q2_1998 VALUES LESS THAN ( TO_DATE(' 1998-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN') )
        PCTFREE 0
        PCTUSED 40
        MAXTRANS 255
        TABLESPACE EXAMPLE
        NOLOGGING
        STORAGE (
        INITIAL 65536
        NEXT 1048576
        PCTINCREASE 0
        MINEXTENTS 1
        MAXEXTENTS 2147483645
        FREELISTS 1
        FREELIST GROUPS 1
        BUFFER_POOL DEFAULT
    )
    COMPRESS ,
    PARTITION SALES_Q3_1998 VALUES LESS THAN ( TO_DATE(' 1998-10-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN') )
        PCTFREE 0
        PCTUSED 40
        MAXTRANS 255
        TABLESPACE EXAMPLE
        NOLOGGING
        STORAGE (
        INITIAL 65536
        NEXT 1048576
        PCTINCREASE 0
        MINEXTENTS 1
        MAXEXTENTS 2147483645
        FREELISTS 1
        FREELIST GROUPS 1
        BUFFER_POOL DEFAULT
    )
    COMPRESS ,
    PARTITION SALES_Q4_1998 VALUES LESS THAN ( TO_DATE(' 1999-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN') )
        PCTFREE 0
        PCTUSED 40
        MAXTRANS 255
        TABLESPACE EXAMPLE
        NOLOGGING
        STORAGE (
        INITIAL 65536
        NEXT 1048576
        PCTINCREASE 0
        MINEXTENTS 1
        MAXEXTENTS 2147483645
        FREELISTS 1
        FREELIST GROUPS 1
        BUFFER_POOL DEFAULT
    )
    COMPRESS ,
    PARTITION SALES_Q1_1999 VALUES LESS THAN ( TO_DATE(' 1999-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN') )
        PCTFREE 0
        PCTUSED 40
        MAXTRANS 255
        TABLESPACE EXAMPLE
        NOLOGGING
        STORAGE (
        INITIAL 65536
        NEXT 1048576
        PCTINCREASE 0
        MINEXTENTS 1
        MAXEXTENTS 2147483645
        FREELISTS 1
        FREELIST GROUPS 1
        BUFFER_POOL DEFAULT
    )
    COMPRESS ,
    PARTITION SALES_Q2_1999 VALUES LESS THAN ( TO_DATE(' 1999-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN') )
        PCTFREE 0
        PCTUSED 40
        MAXTRANS 255
        TABLESPACE EXAMPLE
        NOLOGGING
        STORAGE (
        INITIAL 65536
        NEXT 1048576
        PCTINCREASE 0
        MINEXTENTS 1
        MAXEXTENTS 2147483645
        FREELISTS 1
        FREELIST GROUPS 1
        BUFFER_POOL DEFAULT
    )
    COMPRESS ,
    PARTITION SALES_Q3_1999 VALUES LESS THAN ( TO_DATE(' 1999-10-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN') )
        PCTFREE 0
        PCTUSED 40
        MAXTRANS 255
        TABLESPACE EXAMPLE
        NOLOGGING
        STORAGE (
        INITIAL 65536
        NEXT 1048576
        PCTINCREASE 0
        MINEXTENTS 1
        MAXEXTENTS 2147483645
        FREELISTS 1
        FREELIST GROUPS 1
        BUFFER_POOL DEFAULT
    )
    COMPRESS ,
    PARTITION SALES_Q4_1999 VALUES LESS THAN ( TO_DATE(' 2000-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN') )
        PCTFREE 0
        PCTUSED 40
        MAXTRANS 255
        TABLESPACE EXAMPLE
        NOLOGGING
        STORAGE (
        INITIAL 65536
        NEXT 1048576
        PCTINCREASE 0
        MINEXTENTS 1
        MAXEXTENTS 2147483645
        FREELISTS 1
        FREELIST GROUPS 1
        BUFFER_POOL DEFAULT
    )
    COMPRESS ,
    PARTITION SALES_Q1_2000 VALUES LESS THAN ( TO_DATE(' 2000-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN') )
        PCTFREE 0
        PCTUSED 40
        MAXTRANS 255
        TABLESPACE EXAMPLE
        NOLOGGING
        STORAGE (
        INITIAL 65536
        NEXT 1048576
        PCTINCREASE 0
        MINEXTENTS 1
        MAXEXTENTS 2147483645
        FREELISTS 1
        FREELIST GROUPS 1
        BUFFER_POOL DEFAULT
    )
    COMPRESS ,
    PARTITION SALES_Q2_2000 VALUES LESS THAN ( TO_DATE(' 2000-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN') )
        PCTFREE 0
        PCTUSED 40
        MAXTRANS 255
        TABLESPACE EXAMPLE
        NOLOGGING
        STORAGE (
        INITIAL 65536
        NEXT 1048576
        PCTINCREASE 0
        MINEXTENTS 1
        MAXEXTENTS 2147483645
        FREELISTS 1
        FREELIST GROUPS 1
        BUFFER_POOL DEFAULT
    )
    COMPRESS ,
    PARTITION SALES_Q3_2000 VALUES LESS THAN ( TO_DATE(' 2000-10-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN') )
        PCTFREE 0
        PCTUSED 40
        MAXTRANS 255
        TABLESPACE EXAMPLE
        NOLOGGING
        STORAGE (
        INITIAL 65536
        NEXT 1048576
        PCTINCREASE 0
        MINEXTENTS 1
        MAXEXTENTS 2147483645
        FREELISTS 1
        FREELIST GROUPS 1
        BUFFER_POOL DEFAULT
    )
    COMPRESS ,
    PARTITION SALES_Q4_2000 VALUES LESS THAN ( TO_DATE(' 2001-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN') )
        PCTFREE 0
        PCTUSED 40
        MAXTRANS 255
        TABLESPACE EXAMPLE
        NOLOGGING
        STORAGE (
        INITIAL 65536
        NEXT 1048576
        PCTINCREASE 0
        MINEXTENTS 1
        MAXEXTENTS 2147483645
        FREELISTS 1
        FREELIST GROUPS 1
        BUFFER_POOL DEFAULT
    )
    COMPRESS ,
    PARTITION SALES_Q1_2001 VALUES LESS THAN ( TO_DATE(' 2001-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN') )
        PCTFREE 0
        PCTUSED 40
        MAXTRANS 255
        TABLESPACE EXAMPLE
        NOLOGGING
        STORAGE (
        INITIAL 65536
        NEXT 1048576
        PCTINCREASE 0
        MINEXTENTS 1
        MAXEXTENTS 2147483645
        FREELISTS 1
        FREELIST GROUPS 1
        BUFFER_POOL DEFAULT
    )
    COMPRESS ,
    PARTITION SALES_Q2_2001 VALUES LESS THAN ( TO_DATE(' 2001-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN') )
        PCTFREE 0
        PCTUSED 40
        MAXTRANS 255
        TABLESPACE EXAMPLE
        NOLOGGING
        STORAGE (
        INITIAL 65536
        NEXT 1048576
        PCTINCREASE 0
        MINEXTENTS 1
        MAXEXTENTS 2147483645
        FREELISTS 1
        FREELIST GROUPS 1
        BUFFER_POOL DEFAULT
    )
    COMPRESS ,
    PARTITION SALES_Q3_2001 VALUES LESS THAN ( TO_DATE(' 2001-10-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN') )
        PCTFREE 0
        PCTUSED 40
        MAXTRANS 255
        TABLESPACE EXAMPLE
        NOLOGGING
        STORAGE (
        INITIAL 65536
        NEXT 1048576
        PCTINCREASE 0
        MINEXTENTS 1
        MAXEXTENTS 2147483645
        FREELISTS 1
        FREELIST GROUPS 1
        BUFFER_POOL DEFAULT
    )
    COMPRESS ,
    PARTITION SALES_Q4_2001 VALUES LESS THAN ( TO_DATE(' 2002-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN') )
        PCTFREE 0
        PCTUSED 40
        MAXTRANS 255
        TABLESPACE EXAMPLE
        NOLOGGING
        STORAGE (
        INITIAL 65536
        NEXT 1048576
        PCTINCREASE 0
        MINEXTENTS 1
        MAXEXTENTS 2147483645
        FREELISTS 1
        FREELIST GROUPS 1
        BUFFER_POOL DEFAULT
    )
    COMPRESS ,
    PARTITION SALES_Q1_2002 VALUES LESS THAN ( TO_DATE(' 2002-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN') )
        PCTFREE 0
        PCTUSED 40
        MAXTRANS 255
        TABLESPACE EXAMPLE
        NOLOGGING
        STORAGE (
        INITIAL 65536
        NEXT 1048576
        PCTINCREASE 0
        MINEXTENTS 1
        MAXEXTENTS 2147483645
        FREELISTS 1
        FREELIST GROUPS 1
        BUFFER_POOL DEFAULT
    )
    COMPRESS ,
    PARTITION SALES_Q2_2002 VALUES LESS THAN ( TO_DATE(' 2002-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN') )
        PCTFREE 0
        PCTUSED 40
        MAXTRANS 255
        TABLESPACE EXAMPLE
        NOLOGGING
        STORAGE (
        INITIAL 65536
        NEXT 1048576
        PCTINCREASE 0
        MINEXTENTS 1
        MAXEXTENTS 2147483645
        FREELISTS 1
        FREELIST GROUPS 1
        BUFFER_POOL DEFAULT
    )
    COMPRESS ,
    PARTITION SALES_Q3_2002 VALUES LESS THAN ( TO_DATE(' 2002-10-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN') )
        PCTFREE 0
        PCTUSED 40
        MAXTRANS 255
        TABLESPACE EXAMPLE
        NOLOGGING
        STORAGE (
        INITIAL 65536
        NEXT 1048576
        PCTINCREASE 0
        MINEXTENTS 1
        MAXEXTENTS 2147483645
        FREELISTS 1
        FREELIST GROUPS 1
        BUFFER_POOL DEFAULT
    )
    COMPRESS ,
    PARTITION SALES_Q4_2002 VALUES LESS THAN ( TO_DATE(' 2003-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN') )
        PCTFREE 0
        PCTUSED 40
        MAXTRANS 255
        TABLESPACE EXAMPLE
        NOLOGGING
        STORAGE (
        INITIAL 65536
        NEXT 1048576
        PCTINCREASE 0
        MINEXTENTS 1
        MAXEXTENTS 2147483645
        FREELISTS 1
        FREELIST GROUPS 1
        BUFFER_POOL DEFAULT
    )
    COMPRESS ,
    PARTITION SALES_Q1_2003 VALUES LESS THAN ( TO_DATE(' 2003-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN') )
        PCTFREE 0
        PCTUSED 40
        MAXTRANS 255
        TABLESPACE EXAMPLE
        NOLOGGING
        STORAGE (
        INITIAL 65536
        NEXT 1048576
        PCTINCREASE 0
        MINEXTENTS 1
        MAXEXTENTS 2147483645
        FREELISTS 1
        FREELIST GROUPS 1
        BUFFER_POOL DEFAULT
    )
    COMPRESS ,
    PARTITION SALES_Q2_2003 VALUES LESS THAN ( TO_DATE(' 2003-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN') )
        PCTFREE 0
        PCTUSED 40
        MAXTRANS 255
        TABLESPACE EXAMPLE
        NOLOGGING
        STORAGE (
        INITIAL 65536
        NEXT 1048576
        PCTINCREASE 0
        MINEXTENTS 1
        MAXEXTENTS 2147483645
        FREELISTS 1
        FREELIST GROUPS 1
        BUFFER_POOL DEFAULT
    )
    COMPRESS ,
    PARTITION SALES_Q3_2003 VALUES LESS THAN ( TO_DATE(' 2003-10-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN') )
        PCTFREE 0
        PCTUSED 40
        MAXTRANS 255
        TABLESPACE EXAMPLE
        NOLOGGING
        STORAGE (
        INITIAL 65536
        NEXT 1048576
        PCTINCREASE 0
        MINEXTENTS 1
        MAXEXTENTS 2147483645
        FREELISTS 1
        FREELIST GROUPS 1
        BUFFER_POOL DEFAULT
    )
    COMPRESS ,
    PARTITION SALES_Q4_2003 VALUES LESS THAN ( TO_DATE(' 2004-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN') )
        PCTFREE 5
        PCTUSED 40
        MAXTRANS 255
        TABLESPACE EXAMPLE
        NOLOGGING
        STORAGE (
        INITIAL 65536
        NEXT 1048576
        PCTINCREASE 0
        MINEXTENTS 1
        MAXEXTENTS 2147483645
        FREELISTS 1
        FREELIST GROUPS 1
        BUFFER_POOL DEFAULT
    )
    NOCOMPRESS
    )
;



COMMENT ON TABLE SH.SALES IS 'facts table, without a primary key; all rows are uniquely identified by the combination of all foreign keys'
;

COMMENT ON COLUMN SH.SALES.PROD_ID IS 'FK to the products dimension table'
;

COMMENT ON COLUMN SH.SALES.CUST_ID IS 'FK to the customers dimension table'
;

COMMENT ON COLUMN SH.SALES.TIME_ID IS 'FK to the times dimension table'
;

COMMENT ON COLUMN SH.SALES.CHANNEL_ID IS 'FK to the channels dimension table'
;

COMMENT ON COLUMN SH.SALES.PROMO_ID IS 'promotion identifier, without FK constraint (intentionally) to show outer join optimization'
;

COMMENT ON COLUMN SH.SALES.QUANTITY_SOLD IS 'product quantity sold with the transaction'
;

COMMENT ON COLUMN SH.SALES.AMOUNT_SOLD IS 'invoiced amount to the customer'
;
CREATE BITMAP INDEX SH.SALES_PROD_BIX ON SH.SALES
    (
     PROD_ID ASC
    )
    LOCAL
    (
    PARTITION SALES_1995
        PCTFREE 10
        MAXTRANS 255
        STORAGE (
            INITIAL 65536
            NEXT 1048576
            PCTINCREASE 0
            MINEXTENTS 1
            MAXEXTENTS 2147483645
            FREELISTS 1
            FREELIST GROUPS 1
            BUFFER_POOL DEFAULT )
        TABLESPACE EXAMPLE
        NOLOGGING ,
    PARTITION SALES_1996
        PCTFREE 10
        MAXTRANS 255
        STORAGE (
            INITIAL 65536
            NEXT 1048576
            PCTINCREASE 0
            MINEXTENTS 1
            MAXEXTENTS 2147483645
            FREELISTS 1
            FREELIST GROUPS 1
            BUFFER_POOL DEFAULT )
        TABLESPACE EXAMPLE
        NOLOGGING ,
    PARTITION SALES_H1_1997
        PCTFREE 10
        MAXTRANS 255
        STORAGE (
            INITIAL 65536
            NEXT 1048576
            PCTINCREASE 0
            MINEXTENTS 1
            MAXEXTENTS 2147483645
            FREELISTS 1
            FREELIST GROUPS 1
            BUFFER_POOL DEFAULT )
        TABLESPACE EXAMPLE
        NOLOGGING ,
    PARTITION SALES_H2_1997
        PCTFREE 10
        MAXTRANS 255
        STORAGE (
            INITIAL 65536
            NEXT 1048576
            PCTINCREASE 0
            MINEXTENTS 1
            MAXEXTENTS 2147483645
            FREELISTS 1
            FREELIST GROUPS 1
            BUFFER_POOL DEFAULT )
        TABLESPACE EXAMPLE
        NOLOGGING ,
    PARTITION SALES_Q1_1998
        PCTFREE 10
        MAXTRANS 255
        STORAGE (
            INITIAL 65536
            NEXT 1048576
            PCTINCREASE 0
            MINEXTENTS 1
            MAXEXTENTS 2147483645
            FREELISTS 1
            FREELIST GROUPS 1
            BUFFER_POOL DEFAULT )
        TABLESPACE EXAMPLE
        NOLOGGING ,
    PARTITION SALES_Q2_1998
        PCTFREE 10
        MAXTRANS 255
        STORAGE (
            INITIAL 65536
            NEXT 1048576
            PCTINCREASE 0
            MINEXTENTS 1
            MAXEXTENTS 2147483645
            FREELISTS 1
            FREELIST GROUPS 1
            BUFFER_POOL DEFAULT )
        TABLESPACE EXAMPLE
        NOLOGGING ,
    PARTITION SALES_Q3_1998
        PCTFREE 10
        MAXTRANS 255
        STORAGE (
            INITIAL 65536
            NEXT 1048576
            PCTINCREASE 0
            MINEXTENTS 1
            MAXEXTENTS 2147483645
            FREELISTS 1
            FREELIST GROUPS 1
            BUFFER_POOL DEFAULT )
        TABLESPACE EXAMPLE
        NOLOGGING ,
    PARTITION SALES_Q4_1998
        PCTFREE 10
        MAXTRANS 255
        STORAGE (
            INITIAL 65536
            NEXT 1048576
            PCTINCREASE 0
            MINEXTENTS 1
            MAXEXTENTS 2147483645
            FREELISTS 1
            FREELIST GROUPS 1
            BUFFER_POOL DEFAULT )
        TABLESPACE EXAMPLE
        NOLOGGING ,
    PARTITION SALES_Q1_1999
        PCTFREE 10
        MAXTRANS 255
        STORAGE (
            INITIAL 65536
            NEXT 1048576
            PCTINCREASE 0
            MINEXTENTS 1
            MAXEXTENTS 2147483645
            FREELISTS 1
            FREELIST GROUPS 1
            BUFFER_POOL DEFAULT )
        TABLESPACE EXAMPLE
        NOLOGGING ,
    PARTITION SALES_Q2_1999
        PCTFREE 10
        MAXTRANS 255
        STORAGE (
            INITIAL 65536
            NEXT 1048576
            PCTINCREASE 0
            MINEXTENTS 1
            MAXEXTENTS 2147483645
            FREELISTS 1
            FREELIST GROUPS 1
            BUFFER_POOL DEFAULT )
        TABLESPACE EXAMPLE
        NOLOGGING ,
    PARTITION SALES_Q3_1999
        PCTFREE 10
        MAXTRANS 255
        STORAGE (
            INITIAL 65536
            NEXT 1048576
            PCTINCREASE 0
            MINEXTENTS 1
            MAXEXTENTS 2147483645
            FREELISTS 1
            FREELIST GROUPS 1
            BUFFER_POOL DEFAULT )
        TABLESPACE EXAMPLE
        NOLOGGING ,
    PARTITION SALES_Q4_1999
        PCTFREE 10
        MAXTRANS 255
        STORAGE (
            INITIAL 65536
            NEXT 1048576
            PCTINCREASE 0
            MINEXTENTS 1
            MAXEXTENTS 2147483645
            FREELISTS 1
            FREELIST GROUPS 1
            BUFFER_POOL DEFAULT )
        TABLESPACE EXAMPLE
        NOLOGGING ,
    PARTITION SALES_Q1_2000
        PCTFREE 10
        MAXTRANS 255
        STORAGE (
            INITIAL 65536
            NEXT 1048576
            PCTINCREASE 0
            MINEXTENTS 1
            MAXEXTENTS 2147483645
            FREELISTS 1
            FREELIST GROUPS 1
            BUFFER_POOL DEFAULT )
        TABLESPACE EXAMPLE
        NOLOGGING ,
    PARTITION SALES_Q2_2000
        PCTFREE 10
        MAXTRANS 255
        STORAGE (
            INITIAL 65536
            NEXT 1048576
            PCTINCREASE 0
            MINEXTENTS 1
            MAXEXTENTS 2147483645
            FREELISTS 1
            FREELIST GROUPS 1
            BUFFER_POOL DEFAULT )
        TABLESPACE EXAMPLE
        NOLOGGING ,
    PARTITION SALES_Q3_2000
        PCTFREE 10
        MAXTRANS 255
        STORAGE (
            INITIAL 65536
            NEXT 1048576
            PCTINCREASE 0
            MINEXTENTS 1
            MAXEXTENTS 2147483645
            FREELISTS 1
            FREELIST GROUPS 1
            BUFFER_POOL DEFAULT )
        TABLESPACE EXAMPLE
        NOLOGGING ,
    PARTITION SALES_Q4_2000
        PCTFREE 10
        MAXTRANS 255
        STORAGE (
            INITIAL 65536
            NEXT 1048576
            PCTINCREASE 0
            MINEXTENTS 1
            MAXEXTENTS 2147483645
            FREELISTS 1
            FREELIST GROUPS 1
            BUFFER_POOL DEFAULT )
        TABLESPACE EXAMPLE
        NOLOGGING ,
    PARTITION SALES_Q1_2001
        PCTFREE 10
        MAXTRANS 255
        STORAGE (
            INITIAL 65536
            NEXT 1048576
            PCTINCREASE 0
            MINEXTENTS 1
            MAXEXTENTS 2147483645
            FREELISTS 1
            FREELIST GROUPS 1
            BUFFER_POOL DEFAULT )
        TABLESPACE EXAMPLE
        NOLOGGING ,
    PARTITION SALES_Q2_2001
        PCTFREE 10
        MAXTRANS 255
        STORAGE (
            INITIAL 65536
            NEXT 1048576
            PCTINCREASE 0
            MINEXTENTS 1
            MAXEXTENTS 2147483645
            FREELISTS 1
            FREELIST GROUPS 1
            BUFFER_POOL DEFAULT )
        TABLESPACE EXAMPLE
        NOLOGGING ,
    PARTITION SALES_Q3_2001
        PCTFREE 10
        MAXTRANS 255
        STORAGE (
            INITIAL 65536
            NEXT 1048576
            PCTINCREASE 0
            MINEXTENTS 1
            MAXEXTENTS 2147483645
            FREELISTS 1
            FREELIST GROUPS 1
            BUFFER_POOL DEFAULT )
        TABLESPACE EXAMPLE
        NOLOGGING ,
    PARTITION SALES_Q4_2001
        PCTFREE 10
        MAXTRANS 255
        STORAGE (
            INITIAL 65536
            NEXT 1048576
            PCTINCREASE 0
            MINEXTENTS 1
            MAXEXTENTS 2147483645
            FREELISTS 1
            FREELIST GROUPS 1
            BUFFER_POOL DEFAULT )
        TABLESPACE EXAMPLE
        NOLOGGING ,
    PARTITION SALES_Q1_2002
        PCTFREE 10
        MAXTRANS 255
        STORAGE (
            INITIAL 65536
            NEXT 1048576
            PCTINCREASE 0
            MINEXTENTS 1
            MAXEXTENTS 2147483645
            FREELISTS 1
            FREELIST GROUPS 1
            BUFFER_POOL DEFAULT )
        TABLESPACE EXAMPLE
        NOLOGGING ,
    PARTITION SALES_Q2_2002
        PCTFREE 10
        MAXTRANS 255
        STORAGE (
            INITIAL 65536
            NEXT 1048576
            PCTINCREASE 0
            MINEXTENTS 1
            MAXEXTENTS 2147483645
            FREELISTS 1
            FREELIST GROUPS 1
            BUFFER_POOL DEFAULT )
        TABLESPACE EXAMPLE
        NOLOGGING ,
    PARTITION SALES_Q3_2002
        PCTFREE 10
        MAXTRANS 255
        STORAGE (
            INITIAL 65536
            NEXT 1048576
            PCTINCREASE 0
            MINEXTENTS 1
            MAXEXTENTS 2147483645
            FREELISTS 1
            FREELIST GROUPS 1
            BUFFER_POOL DEFAULT )
        TABLESPACE EXAMPLE
        NOLOGGING ,
    PARTITION SALES_Q4_2002
        PCTFREE 10
        MAXTRANS 255
        STORAGE (
            INITIAL 65536
            NEXT 1048576
            PCTINCREASE 0
            MINEXTENTS 1
            MAXEXTENTS 2147483645
            FREELISTS 1
            FREELIST GROUPS 1
            BUFFER_POOL DEFAULT )
        TABLESPACE EXAMPLE
        NOLOGGING ,
    PARTITION SALES_Q1_2003
        PCTFREE 10
        MAXTRANS 255
        STORAGE (
            INITIAL 65536
            NEXT 1048576
            PCTINCREASE 0
            MINEXTENTS 1
            MAXEXTENTS 2147483645
            FREELISTS 1
            FREELIST GROUPS 1
            BUFFER_POOL DEFAULT )
        TABLESPACE EXAMPLE
        NOLOGGING ,
    PARTITION SALES_Q2_2003
        PCTFREE 10
        MAXTRANS 255
        STORAGE (
            INITIAL 65536
            NEXT 1048576
            PCTINCREASE 0
            MINEXTENTS 1
            MAXEXTENTS 2147483645
            FREELISTS 1
            FREELIST GROUPS 1
            BUFFER_POOL DEFAULT )
        TABLESPACE EXAMPLE
        NOLOGGING ,
    PARTITION SALES_Q3_2003
        PCTFREE 10
        MAXTRANS 255
        STORAGE (
            INITIAL 65536
            NEXT 1048576
            PCTINCREASE 0
            MINEXTENTS 1
            MAXEXTENTS 2147483645
            FREELISTS 1
            FREELIST GROUPS 1
            BUFFER_POOL DEFAULT )
        TABLESPACE EXAMPLE
        NOLOGGING ,
    PARTITION SALES_Q4_2003
        PCTFREE 10
        MAXTRANS 255
        STORAGE (
            INITIAL 65536
            NEXT 1048576
            PCTINCREASE 0
            MINEXTENTS 1
            MAXEXTENTS 2147483645
            FREELISTS 1
            FREELIST GROUPS 1
            BUFFER_POOL DEFAULT )
        TABLESPACE EXAMPLE
        NOLOGGING
    )
    PCTFREE 10
    MAXTRANS 255
    STORAGE (
        PCTINCREASE 0
        MINEXTENTS 1
        MAXEXTENTS UNLIMITED
        FREELISTS 1
        FREELIST GROUPS 1
        BUFFER_POOL DEFAULT )
    NOLOGGING
;
CREATE BITMAP INDEX SH.SALES_CUST_BIX ON SH.SALES
    (
     CUST_ID ASC
    )
    LOCAL
    (
    PARTITION SALES_1995
        PCTFREE 10
        MAXTRANS 255
        STORAGE (
            INITIAL 65536
            NEXT 1048576
            PCTINCREASE 0
            MINEXTENTS 1
            MAXEXTENTS 2147483645
            FREELISTS 1
            FREELIST GROUPS 1
            BUFFER_POOL DEFAULT )
        TABLESPACE EXAMPLE
        NOLOGGING ,
    PARTITION SALES_1996
        PCTFREE 10
        MAXTRANS 255
        STORAGE (
            INITIAL 65536
            NEXT 1048576
            PCTINCREASE 0
            MINEXTENTS 1
            MAXEXTENTS 2147483645
            FREELISTS 1
            FREELIST GROUPS 1
            BUFFER_POOL DEFAULT )
        TABLESPACE EXAMPLE
        NOLOGGING ,
    PARTITION SALES_H1_1997
        PCTFREE 10
        MAXTRANS 255
        STORAGE (
            INITIAL 65536
            NEXT 1048576
            PCTINCREASE 0
            MINEXTENTS 1
            MAXEXTENTS 2147483645
            FREELISTS 1
            FREELIST GROUPS 1
            BUFFER_POOL DEFAULT )
        TABLESPACE EXAMPLE
        NOLOGGING ,
    PARTITION SALES_H2_1997
        PCTFREE 10
        MAXTRANS 255
        STORAGE (
            INITIAL 65536
            NEXT 1048576
            PCTINCREASE 0
            MINEXTENTS 1
            MAXEXTENTS 2147483645
            FREELISTS 1
            FREELIST GROUPS 1
            BUFFER_POOL DEFAULT )
        TABLESPACE EXAMPLE
        NOLOGGING ,
    PARTITION SALES_Q1_1998
        PCTFREE 10
        MAXTRANS 255
        STORAGE (
            INITIAL 65536
            NEXT 1048576
            PCTINCREASE 0
            MINEXTENTS 1
            MAXEXTENTS 2147483645
            FREELISTS 1
            FREELIST GROUPS 1
            BUFFER_POOL DEFAULT )
        TABLESPACE EXAMPLE
        NOLOGGING ,
    PARTITION SALES_Q2_1998
        PCTFREE 10
        MAXTRANS 255
        STORAGE (
            INITIAL 65536
            NEXT 1048576
            PCTINCREASE 0
            MINEXTENTS 1
            MAXEXTENTS 2147483645
            FREELISTS 1
            FREELIST GROUPS 1
            BUFFER_POOL DEFAULT )
        TABLESPACE EXAMPLE
        NOLOGGING ,
    PARTITION SALES_Q3_1998
        PCTFREE 10
        MAXTRANS 255
        STORAGE (
            INITIAL 65536
            NEXT 1048576
            PCTINCREASE 0
            MINEXTENTS 1
            MAXEXTENTS 2147483645
            FREELISTS 1
            FREELIST GROUPS 1
            BUFFER_POOL DEFAULT )
        TABLESPACE EXAMPLE
        NOLOGGING ,
    PARTITION SALES_Q4_1998
        PCTFREE 10
        MAXTRANS 255
        STORAGE (
            INITIAL 65536
            NEXT 1048576
            PCTINCREASE 0
            MINEXTENTS 1
            MAXEXTENTS 2147483645
            FREELISTS 1
            FREELIST GROUPS 1
            BUFFER_POOL DEFAULT )
        TABLESPACE EXAMPLE
        NOLOGGING ,
    PARTITION SALES_Q1_1999
        PCTFREE 10
        MAXTRANS 255
        STORAGE (
            INITIAL 65536
            NEXT 1048576
            PCTINCREASE 0
            MINEXTENTS 1
            MAXEXTENTS 2147483645
            FREELISTS 1
            FREELIST GROUPS 1
            BUFFER_POOL DEFAULT )
        TABLESPACE EXAMPLE
        NOLOGGING ,
    PARTITION SALES_Q2_1999
        PCTFREE 10
        MAXTRANS 255
        STORAGE (
            INITIAL 65536
            NEXT 1048576
            PCTINCREASE 0
            MINEXTENTS 1
            MAXEXTENTS 2147483645
            FREELISTS 1
            FREELIST GROUPS 1
            BUFFER_POOL DEFAULT )
        TABLESPACE EXAMPLE
        NOLOGGING ,
    PARTITION SALES_Q3_1999
        PCTFREE 10
        MAXTRANS 255
        STORAGE (
            INITIAL 65536
            NEXT 1048576
            PCTINCREASE 0
            MINEXTENTS 1
            MAXEXTENTS 2147483645
            FREELISTS 1
            FREELIST GROUPS 1
            BUFFER_POOL DEFAULT )
        TABLESPACE EXAMPLE
        NOLOGGING ,
    PARTITION SALES_Q4_1999
        PCTFREE 10
        MAXTRANS 255
        STORAGE (
            INITIAL 65536
            NEXT 1048576
            PCTINCREASE 0
            MINEXTENTS 1
            MAXEXTENTS 2147483645
            FREELISTS 1
            FREELIST GROUPS 1
            BUFFER_POOL DEFAULT )
        TABLESPACE EXAMPLE
        NOLOGGING ,
    PARTITION SALES_Q1_2000
        PCTFREE 10
        MAXTRANS 255
        STORAGE (
            INITIAL 65536
            NEXT 1048576
            PCTINCREASE 0
            MINEXTENTS 1
            MAXEXTENTS 2147483645
            FREELISTS 1
            FREELIST GROUPS 1
            BUFFER_POOL DEFAULT )
        TABLESPACE EXAMPLE
        NOLOGGING ,
    PARTITION SALES_Q2_2000
        PCTFREE 10
        MAXTRANS 255
        STORAGE (
            INITIAL 65536
            NEXT 1048576
            PCTINCREASE 0
            MINEXTENTS 1
            MAXEXTENTS 2147483645
            FREELISTS 1
            FREELIST GROUPS 1
            BUFFER_POOL DEFAULT )
        TABLESPACE EXAMPLE
        NOLOGGING ,
    PARTITION SALES_Q3_2000
        PCTFREE 10
        MAXTRANS 255
        STORAGE (
            INITIAL 65536
            NEXT 1048576
            PCTINCREASE 0
            MINEXTENTS 1
            MAXEXTENTS 2147483645
            FREELISTS 1
            FREELIST GROUPS 1
            BUFFER_POOL DEFAULT )
        TABLESPACE EXAMPLE
        NOLOGGING ,
    PARTITION SALES_Q4_2000
        PCTFREE 10
        MAXTRANS 255
        STORAGE (
            INITIAL 65536
            NEXT 1048576
            PCTINCREASE 0
            MINEXTENTS 1
            MAXEXTENTS 2147483645
            FREELISTS 1
            FREELIST GROUPS 1
            BUFFER_POOL DEFAULT )
        TABLESPACE EXAMPLE
        NOLOGGING ,
    PARTITION SALES_Q1_2001
        PCTFREE 10
        MAXTRANS 255
        STORAGE (
            INITIAL 65536
            NEXT 1048576
            PCTINCREASE 0
            MINEXTENTS 1
            MAXEXTENTS 2147483645
            FREELISTS 1
            FREELIST GROUPS 1
            BUFFER_POOL DEFAULT )
        TABLESPACE EXAMPLE
        NOLOGGING ,
    PARTITION SALES_Q2_2001
        PCTFREE 10
        MAXTRANS 255
        STORAGE (
            INITIAL 65536
            NEXT 1048576
            PCTINCREASE 0
            MINEXTENTS 1
            MAXEXTENTS 2147483645
            FREELISTS 1
            FREELIST GROUPS 1
            BUFFER_POOL DEFAULT )
        TABLESPACE EXAMPLE
        NOLOGGING ,
    PARTITION SALES_Q3_2001
        PCTFREE 10
        MAXTRANS 255
        STORAGE (
            INITIAL 65536
            NEXT 1048576
            PCTINCREASE 0
            MINEXTENTS 1
            MAXEXTENTS 2147483645
            FREELISTS 1
            FREELIST GROUPS 1
            BUFFER_POOL DEFAULT )
        TABLESPACE EXAMPLE
        NOLOGGING ,
    PARTITION SALES_Q4_2001
        PCTFREE 10
        MAXTRANS 255
        STORAGE (
            INITIAL 65536
            NEXT 1048576
            PCTINCREASE 0
            MINEXTENTS 1
            MAXEXTENTS 2147483645
            FREELISTS 1
            FREELIST GROUPS 1
            BUFFER_POOL DEFAULT )
        TABLESPACE EXAMPLE
        NOLOGGING ,
    PARTITION SALES_Q1_2002
        PCTFREE 10
        MAXTRANS 255
        STORAGE (
            INITIAL 65536
            NEXT 1048576
            PCTINCREASE 0
            MINEXTENTS 1
            MAXEXTENTS 2147483645
            FREELISTS 1
            FREELIST GROUPS 1
            BUFFER_POOL DEFAULT )
        TABLESPACE EXAMPLE
        NOLOGGING ,
    PARTITION SALES_Q2_2002
        PCTFREE 10
        MAXTRANS 255
        STORAGE (
            INITIAL 65536
            NEXT 1048576
            PCTINCREASE 0
            MINEXTENTS 1
            MAXEXTENTS 2147483645
            FREELISTS 1
            FREELIST GROUPS 1
            BUFFER_POOL DEFAULT )
        TABLESPACE EXAMPLE
        NOLOGGING ,
    PARTITION SALES_Q3_2002
        PCTFREE 10
        MAXTRANS 255
        STORAGE (
            INITIAL 65536
            NEXT 1048576
            PCTINCREASE 0
            MINEXTENTS 1
            MAXEXTENTS 2147483645
            FREELISTS 1
            FREELIST GROUPS 1
            BUFFER_POOL DEFAULT )
        TABLESPACE EXAMPLE
        NOLOGGING ,
    PARTITION SALES_Q4_2002
        PCTFREE 10
        MAXTRANS 255
        STORAGE (
            INITIAL 65536
            NEXT 1048576
            PCTINCREASE 0
            MINEXTENTS 1
            MAXEXTENTS 2147483645
            FREELISTS 1
            FREELIST GROUPS 1
            BUFFER_POOL DEFAULT )
        TABLESPACE EXAMPLE
        NOLOGGING ,
    PARTITION SALES_Q1_2003
        PCTFREE 10
        MAXTRANS 255
        STORAGE (
            INITIAL 65536
            NEXT 1048576
            PCTINCREASE 0
            MINEXTENTS 1
            MAXEXTENTS 2147483645
            FREELISTS 1
            FREELIST GROUPS 1
            BUFFER_POOL DEFAULT )
        TABLESPACE EXAMPLE
        NOLOGGING ,
    PARTITION SALES_Q2_2003
        PCTFREE 10
        MAXTRANS 255
        STORAGE (
            INITIAL 65536
            NEXT 1048576
            PCTINCREASE 0
            MINEXTENTS 1
            MAXEXTENTS 2147483645
            FREELISTS 1
            FREELIST GROUPS 1
            BUFFER_POOL DEFAULT )
        TABLESPACE EXAMPLE
        NOLOGGING ,
    PARTITION SALES_Q3_2003
        PCTFREE 10
        MAXTRANS 255
        STORAGE (
            INITIAL 65536
            NEXT 1048576
            PCTINCREASE 0
            MINEXTENTS 1
            MAXEXTENTS 2147483645
            FREELISTS 1
            FREELIST GROUPS 1
            BUFFER_POOL DEFAULT )
        TABLESPACE EXAMPLE
        NOLOGGING ,
    PARTITION SALES_Q4_2003
        PCTFREE 10
        MAXTRANS 255
        STORAGE (
            INITIAL 65536
            NEXT 1048576
            PCTINCREASE 0
            MINEXTENTS 1
            MAXEXTENTS 2147483645
            FREELISTS 1
            FREELIST GROUPS 1
            BUFFER_POOL DEFAULT )
        TABLESPACE EXAMPLE
        NOLOGGING
    )
    PCTFREE 10
    MAXTRANS 255
    STORAGE (
        PCTINCREASE 0
        MINEXTENTS 1
        MAXEXTENTS UNLIMITED
        FREELISTS 1
        FREELIST GROUPS 1
        BUFFER_POOL DEFAULT )
    NOLOGGING
;
CREATE BITMAP INDEX SH.SALES_TIME_BIX ON SH.SALES
    (
     TIME_ID ASC
    )
    LOCAL
    (
    PARTITION SALES_1995
        PCTFREE 10
        MAXTRANS 255
        STORAGE (
            INITIAL 65536
            NEXT 1048576
            PCTINCREASE 0
            MINEXTENTS 1
            MAXEXTENTS 2147483645
            FREELISTS 1
            FREELIST GROUPS 1
            BUFFER_POOL DEFAULT )
        TABLESPACE EXAMPLE
        NOLOGGING ,
    PARTITION SALES_1996
        PCTFREE 10
        MAXTRANS 255
        STORAGE (
            INITIAL 65536
            NEXT 1048576
            PCTINCREASE 0
            MINEXTENTS 1
            MAXEXTENTS 2147483645
            FREELISTS 1
            FREELIST GROUPS 1
            BUFFER_POOL DEFAULT )
        TABLESPACE EXAMPLE
        NOLOGGING ,
    PARTITION SALES_H1_1997
        PCTFREE 10
        MAXTRANS 255
        STORAGE (
            INITIAL 65536
            NEXT 1048576
            PCTINCREASE 0
            MINEXTENTS 1
            MAXEXTENTS 2147483645
            FREELISTS 1
            FREELIST GROUPS 1
            BUFFER_POOL DEFAULT )
        TABLESPACE EXAMPLE
        NOLOGGING ,
    PARTITION SALES_H2_1997
        PCTFREE 10
        MAXTRANS 255
        STORAGE (
            INITIAL 65536
            NEXT 1048576
            PCTINCREASE 0
            MINEXTENTS 1
            MAXEXTENTS 2147483645
            FREELISTS 1
            FREELIST GROUPS 1
            BUFFER_POOL DEFAULT )
        TABLESPACE EXAMPLE
        NOLOGGING ,
    PARTITION SALES_Q1_1998
        PCTFREE 10
        MAXTRANS 255
        STORAGE (
            INITIAL 65536
            NEXT 1048576
            PCTINCREASE 0
            MINEXTENTS 1
            MAXEXTENTS 2147483645
            FREELISTS 1
            FREELIST GROUPS 1
            BUFFER_POOL DEFAULT )
        TABLESPACE EXAMPLE
        NOLOGGING ,
    PARTITION SALES_Q2_1998
        PCTFREE 10
        MAXTRANS 255
        STORAGE (
            INITIAL 65536
            NEXT 1048576
            PCTINCREASE 0
            MINEXTENTS 1
            MAXEXTENTS 2147483645
            FREELISTS 1
            FREELIST GROUPS 1
            BUFFER_POOL DEFAULT )
        TABLESPACE EXAMPLE
        NOLOGGING ,
    PARTITION SALES_Q3_1998
        PCTFREE 10
        MAXTRANS 255
        STORAGE (
            INITIAL 65536
            NEXT 1048576
            PCTINCREASE 0
            MINEXTENTS 1
            MAXEXTENTS 2147483645
            FREELISTS 1
            FREELIST GROUPS 1
            BUFFER_POOL DEFAULT )
        TABLESPACE EXAMPLE
        NOLOGGING ,
    PARTITION SALES_Q4_1998
        PCTFREE 10
        MAXTRANS 255
        STORAGE (
            INITIAL 65536
            NEXT 1048576
            PCTINCREASE 0
            MINEXTENTS 1
            MAXEXTENTS 2147483645
            FREELISTS 1
            FREELIST GROUPS 1
            BUFFER_POOL DEFAULT )
        TABLESPACE EXAMPLE
        NOLOGGING ,
    PARTITION SALES_Q1_1999
        PCTFREE 10
        MAXTRANS 255
        STORAGE (
            INITIAL 65536
            NEXT 1048576
            PCTINCREASE 0
            MINEXTENTS 1
            MAXEXTENTS 2147483645
            FREELISTS 1
            FREELIST GROUPS 1
            BUFFER_POOL DEFAULT )
        TABLESPACE EXAMPLE
        NOLOGGING ,
    PARTITION SALES_Q2_1999
        PCTFREE 10
        MAXTRANS 255
        STORAGE (
            INITIAL 65536
            NEXT 1048576
            PCTINCREASE 0
            MINEXTENTS 1
            MAXEXTENTS 2147483645
            FREELISTS 1
            FREELIST GROUPS 1
            BUFFER_POOL DEFAULT )
        TABLESPACE EXAMPLE
        NOLOGGING ,
    PARTITION SALES_Q3_1999
        PCTFREE 10
        MAXTRANS 255
        STORAGE (
            INITIAL 65536
            NEXT 1048576
            PCTINCREASE 0
            MINEXTENTS 1
            MAXEXTENTS 2147483645
            FREELISTS 1
            FREELIST GROUPS 1
            BUFFER_POOL DEFAULT )
        TABLESPACE EXAMPLE
        NOLOGGING ,
    PARTITION SALES_Q4_1999
        PCTFREE 10
        MAXTRANS 255
        STORAGE (
            INITIAL 65536
            NEXT 1048576
            PCTINCREASE 0
            MINEXTENTS 1
            MAXEXTENTS 2147483645
            FREELISTS 1
            FREELIST GROUPS 1
            BUFFER_POOL DEFAULT )
        TABLESPACE EXAMPLE
        NOLOGGING ,
    PARTITION SALES_Q1_2000
        PCTFREE 10
        MAXTRANS 255
        STORAGE (
            INITIAL 65536
            NEXT 1048576
            PCTINCREASE 0
            MINEXTENTS 1
            MAXEXTENTS 2147483645
            FREELISTS 1
            FREELIST GROUPS 1
            BUFFER_POOL DEFAULT )
        TABLESPACE EXAMPLE
        NOLOGGING ,
    PARTITION SALES_Q2_2000
        PCTFREE 10
        MAXTRANS 255
        STORAGE (
            INITIAL 65536
            NEXT 1048576
            PCTINCREASE 0
            MINEXTENTS 1
            MAXEXTENTS 2147483645
            FREELISTS 1
            FREELIST GROUPS 1
            BUFFER_POOL DEFAULT )
        TABLESPACE EXAMPLE
        NOLOGGING ,
    PARTITION SALES_Q3_2000
        PCTFREE 10
        MAXTRANS 255
        STORAGE (
            INITIAL 65536
            NEXT 1048576
            PCTINCREASE 0
            MINEXTENTS 1
            MAXEXTENTS 2147483645
            FREELISTS 1
            FREELIST GROUPS 1
            BUFFER_POOL DEFAULT )
        TABLESPACE EXAMPLE
        NOLOGGING ,
    PARTITION SALES_Q4_2000
        PCTFREE 10
        MAXTRANS 255
        STORAGE (
            INITIAL 65536
            NEXT 1048576
            PCTINCREASE 0
            MINEXTENTS 1
            MAXEXTENTS 2147483645
            FREELISTS 1
            FREELIST GROUPS 1
            BUFFER_POOL DEFAULT )
        TABLESPACE EXAMPLE
        NOLOGGING ,
    PARTITION SALES_Q1_2001
        PCTFREE 10
        MAXTRANS 255
        STORAGE (
            INITIAL 65536
            NEXT 1048576
            PCTINCREASE 0
            MINEXTENTS 1
            MAXEXTENTS 2147483645
            FREELISTS 1
            FREELIST GROUPS 1
            BUFFER_POOL DEFAULT )
        TABLESPACE EXAMPLE
        NOLOGGING ,
    PARTITION SALES_Q2_2001
        PCTFREE 10
        MAXTRANS 255
        STORAGE (
            INITIAL 65536
            NEXT 1048576
            PCTINCREASE 0
            MINEXTENTS 1
            MAXEXTENTS 2147483645
            FREELISTS 1
            FREELIST GROUPS 1
            BUFFER_POOL DEFAULT )
        TABLESPACE EXAMPLE
        NOLOGGING ,
    PARTITION SALES_Q3_2001
        PCTFREE 10
        MAXTRANS 255
        STORAGE (
            INITIAL 65536
            NEXT 1048576
            PCTINCREASE 0
            MINEXTENTS 1
            MAXEXTENTS 2147483645
            FREELISTS 1
            FREELIST GROUPS 1
            BUFFER_POOL DEFAULT )
        TABLESPACE EXAMPLE
        NOLOGGING ,
    PARTITION SALES_Q4_2001
        PCTFREE 10
        MAXTRANS 255
        STORAGE (
            INITIAL 65536
            NEXT 1048576
            PCTINCREASE 0
            MINEXTENTS 1
            MAXEXTENTS 2147483645
            FREELISTS 1
            FREELIST GROUPS 1
            BUFFER_POOL DEFAULT )
        TABLESPACE EXAMPLE
        NOLOGGING ,
    PARTITION SALES_Q1_2002
        PCTFREE 10
        MAXTRANS 255
        STORAGE (
            INITIAL 65536
            NEXT 1048576
            PCTINCREASE 0
            MINEXTENTS 1
            MAXEXTENTS 2147483645
            FREELISTS 1
            FREELIST GROUPS 1
            BUFFER_POOL DEFAULT )
        TABLESPACE EXAMPLE
        NOLOGGING ,
    PARTITION SALES_Q2_2002
        PCTFREE 10
        MAXTRANS 255
        STORAGE (
            INITIAL 65536
            NEXT 1048576
            PCTINCREASE 0
            MINEXTENTS 1
            MAXEXTENTS 2147483645
            FREELISTS 1
            FREELIST GROUPS 1
            BUFFER_POOL DEFAULT )
        TABLESPACE EXAMPLE
        NOLOGGING ,
    PARTITION SALES_Q3_2002
        PCTFREE 10
        MAXTRANS 255
        STORAGE (
            INITIAL 65536
            NEXT 1048576
            PCTINCREASE 0
            MINEXTENTS 1
            MAXEXTENTS 2147483645
            FREELISTS 1
            FREELIST GROUPS 1
            BUFFER_POOL DEFAULT )
        TABLESPACE EXAMPLE
        NOLOGGING ,
    PARTITION SALES_Q4_2002
        PCTFREE 10
        MAXTRANS 255
        STORAGE (
            INITIAL 65536
            NEXT 1048576
            PCTINCREASE 0
            MINEXTENTS 1
            MAXEXTENTS 2147483645
            FREELISTS 1
            FREELIST GROUPS 1
            BUFFER_POOL DEFAULT )
        TABLESPACE EXAMPLE
        NOLOGGING ,
    PARTITION SALES_Q1_2003
        PCTFREE 10
        MAXTRANS 255
        STORAGE (
            INITIAL 65536
            NEXT 1048576
            PCTINCREASE 0
            MINEXTENTS 1
            MAXEXTENTS 2147483645
            FREELISTS 1
            FREELIST GROUPS 1
            BUFFER_POOL DEFAULT )
        TABLESPACE EXAMPLE
        NOLOGGING ,
    PARTITION SALES_Q2_2003
        PCTFREE 10
        MAXTRANS 255
        STORAGE (
            INITIAL 65536
            NEXT 1048576
            PCTINCREASE 0
            MINEXTENTS 1
            MAXEXTENTS 2147483645
            FREELISTS 1
            FREELIST GROUPS 1
            BUFFER_POOL DEFAULT )
        TABLESPACE EXAMPLE
        NOLOGGING ,
    PARTITION SALES_Q3_2003
        PCTFREE 10
        MAXTRANS 255
        STORAGE (
            INITIAL 65536
            NEXT 1048576
            PCTINCREASE 0
            MINEXTENTS 1
            MAXEXTENTS 2147483645
            FREELISTS 1
            FREELIST GROUPS 1
            BUFFER_POOL DEFAULT )
        TABLESPACE EXAMPLE
        NOLOGGING ,
    PARTITION SALES_Q4_2003
        PCTFREE 10
        MAXTRANS 255
        STORAGE (
            INITIAL 65536
            NEXT 1048576
            PCTINCREASE 0
            MINEXTENTS 1
            MAXEXTENTS 2147483645
            FREELISTS 1
            FREELIST GROUPS 1
            BUFFER_POOL DEFAULT )
        TABLESPACE EXAMPLE
        NOLOGGING
    )
    PCTFREE 10
    MAXTRANS 255
    STORAGE (
        PCTINCREASE 0
        MINEXTENTS 1
        MAXEXTENTS UNLIMITED
        FREELISTS 1
        FREELIST GROUPS 1
        BUFFER_POOL DEFAULT )
    NOLOGGING
;
CREATE BITMAP INDEX SH.SALES_CHANNEL_BIX ON SH.SALES
    (
     CHANNEL_ID ASC
    )
    LOCAL
    (
    PARTITION SALES_1995
        PCTFREE 10
        MAXTRANS 255
        STORAGE (
            INITIAL 65536
            NEXT 1048576
            PCTINCREASE 0
            MINEXTENTS 1
            MAXEXTENTS 2147483645
            FREELISTS 1
            FREELIST GROUPS 1
            BUFFER_POOL DEFAULT )
        TABLESPACE EXAMPLE
        NOLOGGING ,
    PARTITION SALES_1996
        PCTFREE 10
        MAXTRANS 255
        STORAGE (
            INITIAL 65536
            NEXT 1048576
            PCTINCREASE 0
            MINEXTENTS 1
            MAXEXTENTS 2147483645
            FREELISTS 1
            FREELIST GROUPS 1
            BUFFER_POOL DEFAULT )
        TABLESPACE EXAMPLE
        NOLOGGING ,
    PARTITION SALES_H1_1997
        PCTFREE 10
        MAXTRANS 255
        STORAGE (
            INITIAL 65536
            NEXT 1048576
            PCTINCREASE 0
            MINEXTENTS 1
            MAXEXTENTS 2147483645
            FREELISTS 1
            FREELIST GROUPS 1
            BUFFER_POOL DEFAULT )
        TABLESPACE EXAMPLE
        NOLOGGING ,
    PARTITION SALES_H2_1997
        PCTFREE 10
        MAXTRANS 255
        STORAGE (
            INITIAL 65536
            NEXT 1048576
            PCTINCREASE 0
            MINEXTENTS 1
            MAXEXTENTS 2147483645
            FREELISTS 1
            FREELIST GROUPS 1
            BUFFER_POOL DEFAULT )
        TABLESPACE EXAMPLE
        NOLOGGING ,
    PARTITION SALES_Q1_1998
        PCTFREE 10
        MAXTRANS 255
        STORAGE (
            INITIAL 65536
            NEXT 1048576
            PCTINCREASE 0
            MINEXTENTS 1
            MAXEXTENTS 2147483645
            FREELISTS 1
            FREELIST GROUPS 1
            BUFFER_POOL DEFAULT )
        TABLESPACE EXAMPLE
        NOLOGGING ,
    PARTITION SALES_Q2_1998
        PCTFREE 10
        MAXTRANS 255
        STORAGE (
            INITIAL 65536
            NEXT 1048576
            PCTINCREASE 0
            MINEXTENTS 1
            MAXEXTENTS 2147483645
            FREELISTS 1
            FREELIST GROUPS 1
            BUFFER_POOL DEFAULT )
        TABLESPACE EXAMPLE
        NOLOGGING ,
    PARTITION SALES_Q3_1998
        PCTFREE 10
        MAXTRANS 255
        STORAGE (
            INITIAL 65536
            NEXT 1048576
            PCTINCREASE 0
            MINEXTENTS 1
            MAXEXTENTS 2147483645
            FREELISTS 1
            FREELIST GROUPS 1
            BUFFER_POOL DEFAULT )
        TABLESPACE EXAMPLE
        NOLOGGING ,
    PARTITION SALES_Q4_1998
        PCTFREE 10
        MAXTRANS 255
        STORAGE (
            INITIAL 65536
            NEXT 1048576
            PCTINCREASE 0
            MINEXTENTS 1
            MAXEXTENTS 2147483645
            FREELISTS 1
            FREELIST GROUPS 1
            BUFFER_POOL DEFAULT )
        TABLESPACE EXAMPLE
        NOLOGGING ,
    PARTITION SALES_Q1_1999
        PCTFREE 10
        MAXTRANS 255
        STORAGE (
            INITIAL 65536
            NEXT 1048576
            PCTINCREASE 0
            MINEXTENTS 1
            MAXEXTENTS 2147483645
            FREELISTS 1
            FREELIST GROUPS 1
            BUFFER_POOL DEFAULT )
        TABLESPACE EXAMPLE
        NOLOGGING ,
    PARTITION SALES_Q2_1999
        PCTFREE 10
        MAXTRANS 255
        STORAGE (
            INITIAL 65536
            NEXT 1048576
            PCTINCREASE 0
            MINEXTENTS 1
            MAXEXTENTS 2147483645
            FREELISTS 1
            FREELIST GROUPS 1
            BUFFER_POOL DEFAULT )
        TABLESPACE EXAMPLE
        NOLOGGING ,
    PARTITION SALES_Q3_1999
        PCTFREE 10
        MAXTRANS 255
        STORAGE (
            INITIAL 65536
            NEXT 1048576
            PCTINCREASE 0
            MINEXTENTS 1
            MAXEXTENTS 2147483645
            FREELISTS 1
            FREELIST GROUPS 1
            BUFFER_POOL DEFAULT )
        TABLESPACE EXAMPLE
        NOLOGGING ,
    PARTITION SALES_Q4_1999
        PCTFREE 10
        MAXTRANS 255
        STORAGE (
            INITIAL 65536
            NEXT 1048576
            PCTINCREASE 0
            MINEXTENTS 1
            MAXEXTENTS 2147483645
            FREELISTS 1
            FREELIST GROUPS 1
            BUFFER_POOL DEFAULT )
        TABLESPACE EXAMPLE
        NOLOGGING ,
    PARTITION SALES_Q1_2000
        PCTFREE 10
        MAXTRANS 255
        STORAGE (
            INITIAL 65536
            NEXT 1048576
            PCTINCREASE 0
            MINEXTENTS 1
            MAXEXTENTS 2147483645
            FREELISTS 1
            FREELIST GROUPS 1
            BUFFER_POOL DEFAULT )
        TABLESPACE EXAMPLE
        NOLOGGING ,
    PARTITION SALES_Q2_2000
        PCTFREE 10
        MAXTRANS 255
        STORAGE (
            INITIAL 65536
            NEXT 1048576
            PCTINCREASE 0
            MINEXTENTS 1
            MAXEXTENTS 2147483645
            FREELISTS 1
            FREELIST GROUPS 1
            BUFFER_POOL DEFAULT )
        TABLESPACE EXAMPLE
        NOLOGGING ,
    PARTITION SALES_Q3_2000
        PCTFREE 10
        MAXTRANS 255
        STORAGE (
            INITIAL 65536
            NEXT 1048576
            PCTINCREASE 0
            MINEXTENTS 1
            MAXEXTENTS 2147483645
            FREELISTS 1
            FREELIST GROUPS 1
            BUFFER_POOL DEFAULT )
        TABLESPACE EXAMPLE
        NOLOGGING ,
    PARTITION SALES_Q4_2000
        PCTFREE 10
        MAXTRANS 255
        STORAGE (
            INITIAL 65536
            NEXT 1048576
            PCTINCREASE 0
            MINEXTENTS 1
            MAXEXTENTS 2147483645
            FREELISTS 1
            FREELIST GROUPS 1
            BUFFER_POOL DEFAULT )
        TABLESPACE EXAMPLE
        NOLOGGING ,
    PARTITION SALES_Q1_2001
        PCTFREE 10
        MAXTRANS 255
        STORAGE (
            INITIAL 65536
            NEXT 1048576
            PCTINCREASE 0
            MINEXTENTS 1
            MAXEXTENTS 2147483645
            FREELISTS 1
            FREELIST GROUPS 1
            BUFFER_POOL DEFAULT )
        TABLESPACE EXAMPLE
        NOLOGGING ,
    PARTITION SALES_Q2_2001
        PCTFREE 10
        MAXTRANS 255
        STORAGE (
            INITIAL 65536
            NEXT 1048576
            PCTINCREASE 0
            MINEXTENTS 1
            MAXEXTENTS 2147483645
            FREELISTS 1
            FREELIST GROUPS 1
            BUFFER_POOL DEFAULT )
        TABLESPACE EXAMPLE
        NOLOGGING ,
    PARTITION SALES_Q3_2001
        PCTFREE 10
        MAXTRANS 255
        STORAGE (
            INITIAL 65536
            NEXT 1048576
            PCTINCREASE 0
            MINEXTENTS 1
            MAXEXTENTS 2147483645
            FREELISTS 1
            FREELIST GROUPS 1
            BUFFER_POOL DEFAULT )
        TABLESPACE EXAMPLE
        NOLOGGING ,
    PARTITION SALES_Q4_2001
        PCTFREE 10
        MAXTRANS 255
        STORAGE (
            INITIAL 65536
            NEXT 1048576
            PCTINCREASE 0
            MINEXTENTS 1
            MAXEXTENTS 2147483645
            FREELISTS 1
            FREELIST GROUPS 1
            BUFFER_POOL DEFAULT )
        TABLESPACE EXAMPLE
        NOLOGGING ,
    PARTITION SALES_Q1_2002
        PCTFREE 10
        MAXTRANS 255
        STORAGE (
            INITIAL 65536
            NEXT 1048576
            PCTINCREASE 0
            MINEXTENTS 1
            MAXEXTENTS 2147483645
            FREELISTS 1
            FREELIST GROUPS 1
            BUFFER_POOL DEFAULT )
        TABLESPACE EXAMPLE
        NOLOGGING ,
    PARTITION SALES_Q2_2002
        PCTFREE 10
        MAXTRANS 255
        STORAGE (
            INITIAL 65536
            NEXT 1048576
            PCTINCREASE 0
            MINEXTENTS 1
            MAXEXTENTS 2147483645
            FREELISTS 1
            FREELIST GROUPS 1
            BUFFER_POOL DEFAULT )
        TABLESPACE EXAMPLE
        NOLOGGING ,
    PARTITION SALES_Q3_2002
        PCTFREE 10
        MAXTRANS 255
        STORAGE (
            INITIAL 65536
            NEXT 1048576
            PCTINCREASE 0
            MINEXTENTS 1
            MAXEXTENTS 2147483645
            FREELISTS 1
            FREELIST GROUPS 1
            BUFFER_POOL DEFAULT )
        TABLESPACE EXAMPLE
        NOLOGGING ,
    PARTITION SALES_Q4_2002
        PCTFREE 10
        MAXTRANS 255
        STORAGE (
            INITIAL 65536
            NEXT 1048576
            PCTINCREASE 0
            MINEXTENTS 1
            MAXEXTENTS 2147483645
            FREELISTS 1
            FREELIST GROUPS 1
            BUFFER_POOL DEFAULT )
        TABLESPACE EXAMPLE
        NOLOGGING ,
    PARTITION SALES_Q1_2003
        PCTFREE 10
        MAXTRANS 255
        STORAGE (
            INITIAL 65536
            NEXT 1048576
            PCTINCREASE 0
            MINEXTENTS 1
            MAXEXTENTS 2147483645
            FREELISTS 1
            FREELIST GROUPS 1
            BUFFER_POOL DEFAULT )
        TABLESPACE EXAMPLE
        NOLOGGING ,
    PARTITION SALES_Q2_2003
        PCTFREE 10
        MAXTRANS 255
        STORAGE (
            INITIAL 65536
            NEXT 1048576
            PCTINCREASE 0
            MINEXTENTS 1
            MAXEXTENTS 2147483645
            FREELISTS 1
            FREELIST GROUPS 1
            BUFFER_POOL DEFAULT )
        TABLESPACE EXAMPLE
        NOLOGGING ,
    PARTITION SALES_Q3_2003
        PCTFREE 10
        MAXTRANS 255
        STORAGE (
            INITIAL 65536
            NEXT 1048576
            PCTINCREASE 0
            MINEXTENTS 1
            MAXEXTENTS 2147483645
            FREELISTS 1
            FREELIST GROUPS 1
            BUFFER_POOL DEFAULT )
        TABLESPACE EXAMPLE
        NOLOGGING ,
    PARTITION SALES_Q4_2003
        PCTFREE 10
        MAXTRANS 255
        STORAGE (
            INITIAL 65536
            NEXT 1048576
            PCTINCREASE 0
            MINEXTENTS 1
            MAXEXTENTS 2147483645
            FREELISTS 1
            FREELIST GROUPS 1
            BUFFER_POOL DEFAULT )
        TABLESPACE EXAMPLE
        NOLOGGING
    )
    PCTFREE 10
    MAXTRANS 255
    STORAGE (
        PCTINCREASE 0
        MINEXTENTS 1
        MAXEXTENTS UNLIMITED
        FREELISTS 1
        FREELIST GROUPS 1
        BUFFER_POOL DEFAULT )
    NOLOGGING
;
CREATE BITMAP INDEX SH.SALES_PROMO_BIX ON SH.SALES
    (
     PROMO_ID ASC
    )
    LOCAL
    (
    PARTITION SALES_1995
        PCTFREE 10
        MAXTRANS 255
        STORAGE (
            INITIAL 65536
            NEXT 1048576
            PCTINCREASE 0
            MINEXTENTS 1
            MAXEXTENTS 2147483645
            FREELISTS 1
            FREELIST GROUPS 1
            BUFFER_POOL DEFAULT )
        TABLESPACE EXAMPLE
        NOLOGGING ,
    PARTITION SALES_1996
        PCTFREE 10
        MAXTRANS 255
        STORAGE (
            INITIAL 65536
            NEXT 1048576
            PCTINCREASE 0
            MINEXTENTS 1
            MAXEXTENTS 2147483645
            FREELISTS 1
            FREELIST GROUPS 1
            BUFFER_POOL DEFAULT )
        TABLESPACE EXAMPLE
        NOLOGGING ,
    PARTITION SALES_H1_1997
        PCTFREE 10
        MAXTRANS 255
        STORAGE (
            INITIAL 65536
            NEXT 1048576
            PCTINCREASE 0
            MINEXTENTS 1
            MAXEXTENTS 2147483645
            FREELISTS 1
            FREELIST GROUPS 1
            BUFFER_POOL DEFAULT )
        TABLESPACE EXAMPLE
        NOLOGGING ,
    PARTITION SALES_H2_1997
        PCTFREE 10
        MAXTRANS 255
        STORAGE (
            INITIAL 65536
            NEXT 1048576
            PCTINCREASE 0
            MINEXTENTS 1
            MAXEXTENTS 2147483645
            FREELISTS 1
            FREELIST GROUPS 1
            BUFFER_POOL DEFAULT )
        TABLESPACE EXAMPLE
        NOLOGGING ,
    PARTITION SALES_Q1_1998
        PCTFREE 10
        MAXTRANS 255
        STORAGE (
            INITIAL 65536
            NEXT 1048576
            PCTINCREASE 0
            MINEXTENTS 1
            MAXEXTENTS 2147483645
            FREELISTS 1
            FREELIST GROUPS 1
            BUFFER_POOL DEFAULT )
        TABLESPACE EXAMPLE
        NOLOGGING ,
    PARTITION SALES_Q2_1998
        PCTFREE 10
        MAXTRANS 255
        STORAGE (
            INITIAL 65536
            NEXT 1048576
            PCTINCREASE 0
            MINEXTENTS 1
            MAXEXTENTS 2147483645
            FREELISTS 1
            FREELIST GROUPS 1
            BUFFER_POOL DEFAULT )
        TABLESPACE EXAMPLE
        NOLOGGING ,
    PARTITION SALES_Q3_1998
        PCTFREE 10
        MAXTRANS 255
        STORAGE (
            INITIAL 65536
            NEXT 1048576
            PCTINCREASE 0
            MINEXTENTS 1
            MAXEXTENTS 2147483645
            FREELISTS 1
            FREELIST GROUPS 1
            BUFFER_POOL DEFAULT )
        TABLESPACE EXAMPLE
        NOLOGGING ,
    PARTITION SALES_Q4_1998
        PCTFREE 10
        MAXTRANS 255
        STORAGE (
            INITIAL 65536
            NEXT 1048576
            PCTINCREASE 0
            MINEXTENTS 1
            MAXEXTENTS 2147483645
            FREELISTS 1
            FREELIST GROUPS 1
            BUFFER_POOL DEFAULT )
        TABLESPACE EXAMPLE
        NOLOGGING ,
    PARTITION SALES_Q1_1999
        PCTFREE 10
        MAXTRANS 255
        STORAGE (
            INITIAL 65536
            NEXT 1048576
            PCTINCREASE 0
            MINEXTENTS 1
            MAXEXTENTS 2147483645
            FREELISTS 1
            FREELIST GROUPS 1
            BUFFER_POOL DEFAULT )
        TABLESPACE EXAMPLE
        NOLOGGING ,
    PARTITION SALES_Q2_1999
        PCTFREE 10
        MAXTRANS 255
        STORAGE (
            INITIAL 65536
            NEXT 1048576
            PCTINCREASE 0
            MINEXTENTS 1
            MAXEXTENTS 2147483645
            FREELISTS 1
            FREELIST GROUPS 1
            BUFFER_POOL DEFAULT )
        TABLESPACE EXAMPLE
        NOLOGGING ,
    PARTITION SALES_Q3_1999
        PCTFREE 10
        MAXTRANS 255
        STORAGE (
            INITIAL 65536
            NEXT 1048576
            PCTINCREASE 0
            MINEXTENTS 1
            MAXEXTENTS 2147483645
            FREELISTS 1
            FREELIST GROUPS 1
            BUFFER_POOL DEFAULT )
        TABLESPACE EXAMPLE
        NOLOGGING ,
    PARTITION SALES_Q4_1999
        PCTFREE 10
        MAXTRANS 255
        STORAGE (
            INITIAL 65536
            NEXT 1048576
            PCTINCREASE 0
            MINEXTENTS 1
            MAXEXTENTS 2147483645
            FREELISTS 1
            FREELIST GROUPS 1
            BUFFER_POOL DEFAULT )
        TABLESPACE EXAMPLE
        NOLOGGING ,
    PARTITION SALES_Q1_2000
        PCTFREE 10
        MAXTRANS 255
        STORAGE (
            INITIAL 65536
            NEXT 1048576
            PCTINCREASE 0
            MINEXTENTS 1
            MAXEXTENTS 2147483645
            FREELISTS 1
            FREELIST GROUPS 1
            BUFFER_POOL DEFAULT )
        TABLESPACE EXAMPLE
        NOLOGGING ,
    PARTITION SALES_Q2_2000
        PCTFREE 10
        MAXTRANS 255
        STORAGE (
            INITIAL 65536
            NEXT 1048576
            PCTINCREASE 0
            MINEXTENTS 1
            MAXEXTENTS 2147483645
            FREELISTS 1
            FREELIST GROUPS 1
            BUFFER_POOL DEFAULT )
        TABLESPACE EXAMPLE
        NOLOGGING ,
    PARTITION SALES_Q3_2000
        PCTFREE 10
        MAXTRANS 255
        STORAGE (
            INITIAL 65536
            NEXT 1048576
            PCTINCREASE 0
            MINEXTENTS 1
            MAXEXTENTS 2147483645
            FREELISTS 1
            FREELIST GROUPS 1
            BUFFER_POOL DEFAULT )
        TABLESPACE EXAMPLE
        NOLOGGING ,
    PARTITION SALES_Q4_2000
        PCTFREE 10
        MAXTRANS 255
        STORAGE (
            INITIAL 65536
            NEXT 1048576
            PCTINCREASE 0
            MINEXTENTS 1
            MAXEXTENTS 2147483645
            FREELISTS 1
            FREELIST GROUPS 1
            BUFFER_POOL DEFAULT )
        TABLESPACE EXAMPLE
        NOLOGGING ,
    PARTITION SALES_Q1_2001
        PCTFREE 10
        MAXTRANS 255
        STORAGE (
            INITIAL 65536
            NEXT 1048576
            PCTINCREASE 0
            MINEXTENTS 1
            MAXEXTENTS 2147483645
            FREELISTS 1
            FREELIST GROUPS 1
            BUFFER_POOL DEFAULT )
        TABLESPACE EXAMPLE
        NOLOGGING ,
    PARTITION SALES_Q2_2001
        PCTFREE 10
        MAXTRANS 255
        STORAGE (
            INITIAL 65536
            NEXT 1048576
            PCTINCREASE 0
            MINEXTENTS 1
            MAXEXTENTS 2147483645
            FREELISTS 1
            FREELIST GROUPS 1
            BUFFER_POOL DEFAULT )
        TABLESPACE EXAMPLE
        NOLOGGING ,
    PARTITION SALES_Q3_2001
        PCTFREE 10
        MAXTRANS 255
        STORAGE (
            INITIAL 65536
            NEXT 1048576
            PCTINCREASE 0
            MINEXTENTS 1
            MAXEXTENTS 2147483645
            FREELISTS 1
            FREELIST GROUPS 1
            BUFFER_POOL DEFAULT )
        TABLESPACE EXAMPLE
        NOLOGGING ,
    PARTITION SALES_Q4_2001
        PCTFREE 10
        MAXTRANS 255
        STORAGE (
            INITIAL 65536
            NEXT 1048576
            PCTINCREASE 0
            MINEXTENTS 1
            MAXEXTENTS 2147483645
            FREELISTS 1
            FREELIST GROUPS 1
            BUFFER_POOL DEFAULT )
        TABLESPACE EXAMPLE
        NOLOGGING ,
    PARTITION SALES_Q1_2002
        PCTFREE 10
        MAXTRANS 255
        STORAGE (
            INITIAL 65536
            NEXT 1048576
            PCTINCREASE 0
            MINEXTENTS 1
            MAXEXTENTS 2147483645
            FREELISTS 1
            FREELIST GROUPS 1
            BUFFER_POOL DEFAULT )
        TABLESPACE EXAMPLE
        NOLOGGING ,
    PARTITION SALES_Q2_2002
        PCTFREE 10
        MAXTRANS 255
        STORAGE (
            INITIAL 65536
            NEXT 1048576
            PCTINCREASE 0
            MINEXTENTS 1
            MAXEXTENTS 2147483645
            FREELISTS 1
            FREELIST GROUPS 1
            BUFFER_POOL DEFAULT )
        TABLESPACE EXAMPLE
        NOLOGGING ,
    PARTITION SALES_Q3_2002
        PCTFREE 10
        MAXTRANS 255
        STORAGE (
            INITIAL 65536
            NEXT 1048576
            PCTINCREASE 0
            MINEXTENTS 1
            MAXEXTENTS 2147483645
            FREELISTS 1
            FREELIST GROUPS 1
            BUFFER_POOL DEFAULT )
        TABLESPACE EXAMPLE
        NOLOGGING ,
    PARTITION SALES_Q4_2002
        PCTFREE 10
        MAXTRANS 255
        STORAGE (
            INITIAL 65536
            NEXT 1048576
            PCTINCREASE 0
            MINEXTENTS 1
            MAXEXTENTS 2147483645
            FREELISTS 1
            FREELIST GROUPS 1
            BUFFER_POOL DEFAULT )
        TABLESPACE EXAMPLE
        NOLOGGING ,
    PARTITION SALES_Q1_2003
        PCTFREE 10
        MAXTRANS 255
        STORAGE (
            INITIAL 65536
            NEXT 1048576
            PCTINCREASE 0
            MINEXTENTS 1
            MAXEXTENTS 2147483645
            FREELISTS 1
            FREELIST GROUPS 1
            BUFFER_POOL DEFAULT )
        TABLESPACE EXAMPLE
        NOLOGGING ,
    PARTITION SALES_Q2_2003
        PCTFREE 10
        MAXTRANS 255
        STORAGE (
            INITIAL 65536
            NEXT 1048576
            PCTINCREASE 0
            MINEXTENTS 1
            MAXEXTENTS 2147483645
            FREELISTS 1
            FREELIST GROUPS 1
            BUFFER_POOL DEFAULT )
        TABLESPACE EXAMPLE
        NOLOGGING ,
    PARTITION SALES_Q3_2003
        PCTFREE 10
        MAXTRANS 255
        STORAGE (
            INITIAL 65536
            NEXT 1048576
            PCTINCREASE 0
            MINEXTENTS 1
            MAXEXTENTS 2147483645
            FREELISTS 1
            FREELIST GROUPS 1
            BUFFER_POOL DEFAULT )
        TABLESPACE EXAMPLE
        NOLOGGING ,
    PARTITION SALES_Q4_2003
        PCTFREE 10
        MAXTRANS 255
        STORAGE (
            INITIAL 65536
            NEXT 1048576
            PCTINCREASE 0
            MINEXTENTS 1
            MAXEXTENTS 2147483645
            FREELISTS 1
            FREELIST GROUPS 1
            BUFFER_POOL DEFAULT )
        TABLESPACE EXAMPLE
        NOLOGGING
    )
    PCTFREE 10
    MAXTRANS 255
    STORAGE (
        PCTINCREASE 0
        MINEXTENTS 1
        MAXEXTENTS UNLIMITED
        FREELISTS 1
        FREELIST GROUPS 1
        BUFFER_POOL DEFAULT )
    NOLOGGING
;


CREATE TABLE SH.SUPPLEMENTARY_DEMOGRAPHICS
    (
     CUST_ID NUMBER  NOT NULL ,
     EDUCATION VARCHAR2 (21 BYTE) ,
     OCCUPATION VARCHAR2 (21 BYTE) ,
     HOUSEHOLD_SIZE VARCHAR2 (21 BYTE) ,
     YRS_RESIDENCE NUMBER ,
     AFFINITY_CARD NUMBER (10) ,
     BULK_PACK_DISKETTES NUMBER (10) ,
     FLAT_PANEL_MONITOR NUMBER (10) ,
     HOME_THEATER_PACKAGE NUMBER (10) ,
     BOOKKEEPING_APPLICATION NUMBER (10) ,
     PRINTER_SUPPLIES NUMBER (10) ,
     Y_BOX_GAMES NUMBER (10) ,
     OS_DOC_SET_KANJI NUMBER (10) ,
     COMMENTS VARCHAR2 (4000 BYTE)
    )
        PCTFREE 10
        PCTUSED 40
        MAXTRANS 255
        TABLESPACE EXAMPLE
        NOLOGGING
        STORAGE (
        INITIAL 65536
        NEXT 1048576
        PCTINCREASE 0
        MINEXTENTS 1
        MAXEXTENTS 2147483645
        FREELISTS 1
        FREELIST GROUPS 1
        BUFFER_POOL DEFAULT
    )
;


CREATE INDEX SH.SUP_TEXT_IDX ON SH.SUPPLEMENTARY_DEMOGRAPHICS
    (
     COMMENTS
    )
    INDEXTYPE IS CTXSYS.CONTEXT
    PARAMETERS ('nopopulate')
;

ALTER TABLE SH.SUPPLEMENTARY_DEMOGRAPHICS
    ADD CONSTRAINT SUPP_DEMO_PK PRIMARY KEY ( CUST_ID ) DISABLE NOVALIDATE ;


CREATE TABLE SH.TIMES
    (
     TIME_ID DATE  NOT NULL ,
     DAY_NAME VARCHAR2 (9 BYTE)  NOT NULL ,
     DAY_NUMBER_IN_WEEK NUMBER (1)  NOT NULL ,
     DAY_NUMBER_IN_MONTH NUMBER (2)  NOT NULL ,
     CALENDAR_WEEK_NUMBER NUMBER (2)  NOT NULL ,
     FISCAL_WEEK_NUMBER NUMBER (2)  NOT NULL ,
     WEEK_ENDING_DAY DATE  NOT NULL ,
     WEEK_ENDING_DAY_ID NUMBER  NOT NULL ,
     CALENDAR_MONTH_NUMBER NUMBER (2)  NOT NULL ,
     FISCAL_MONTH_NUMBER NUMBER (2)  NOT NULL ,
     CALENDAR_MONTH_DESC VARCHAR2 (8 BYTE)  NOT NULL ,
     CALENDAR_MONTH_ID NUMBER  NOT NULL ,
     FISCAL_MONTH_DESC VARCHAR2 (8 BYTE)  NOT NULL ,
     FISCAL_MONTH_ID NUMBER  NOT NULL ,
     DAYS_IN_CAL_MONTH NUMBER  NOT NULL ,
     DAYS_IN_FIS_MONTH NUMBER  NOT NULL ,
     END_OF_CAL_MONTH DATE  NOT NULL ,
     END_OF_FIS_MONTH DATE  NOT NULL ,
     CALENDAR_MONTH_NAME VARCHAR2 (9 BYTE)  NOT NULL ,
     FISCAL_MONTH_NAME VARCHAR2 (9 BYTE)  NOT NULL ,
     CALENDAR_QUARTER_DESC CHAR (7 BYTE)  NOT NULL ,
     CALENDAR_QUARTER_ID NUMBER  NOT NULL ,
     FISCAL_QUARTER_DESC CHAR (7 BYTE)  NOT NULL ,
     FISCAL_QUARTER_ID NUMBER  NOT NULL ,
     DAYS_IN_CAL_QUARTER NUMBER  NOT NULL ,
     DAYS_IN_FIS_QUARTER NUMBER  NOT NULL ,
     END_OF_CAL_QUARTER DATE  NOT NULL ,
     END_OF_FIS_QUARTER DATE  NOT NULL ,
     CALENDAR_QUARTER_NUMBER NUMBER (1)  NOT NULL ,
     FISCAL_QUARTER_NUMBER NUMBER (1)  NOT NULL ,
     CALENDAR_YEAR NUMBER (4)  NOT NULL ,
     CALENDAR_YEAR_ID NUMBER  NOT NULL ,
     FISCAL_YEAR NUMBER (4)  NOT NULL ,
     FISCAL_YEAR_ID NUMBER  NOT NULL ,
     DAYS_IN_CAL_YEAR NUMBER  NOT NULL ,
     DAYS_IN_FIS_YEAR NUMBER  NOT NULL ,
     END_OF_CAL_YEAR DATE  NOT NULL ,
     END_OF_FIS_YEAR DATE  NOT NULL
    )
        PCTFREE 10
        PCTUSED 40
        MAXTRANS 255
        TABLESPACE EXAMPLE
        NOLOGGING
        STORAGE (
        INITIAL 65536
        NEXT 1048576
        PCTINCREASE 0
        MINEXTENTS 1
        MAXEXTENTS 2147483645
        FREELISTS 1
        FREELIST GROUPS 1
        BUFFER_POOL DEFAULT
    )
;



COMMENT ON TABLE SH.TIMES IS 'Time dimension table to support multiple hierarchies and materialized views'
;

COMMENT ON COLUMN SH.TIMES.TIME_ID IS 'primary key; day date, finest granularity, CORRECT ORDER'
;

COMMENT ON COLUMN SH.TIMES.DAY_NAME IS 'Monday to Sunday, repeating'
;

COMMENT ON COLUMN SH.TIMES.DAY_NUMBER_IN_WEEK IS '1 to 7, repeating'
;

COMMENT ON COLUMN SH.TIMES.DAY_NUMBER_IN_MONTH IS '1 to 31, repeating'
;

COMMENT ON COLUMN SH.TIMES.CALENDAR_WEEK_NUMBER IS '1 to 53, repeating'
;

COMMENT ON COLUMN SH.TIMES.FISCAL_WEEK_NUMBER IS '1 to 53, repeating'
;

COMMENT ON COLUMN SH.TIMES.WEEK_ENDING_DAY IS 'date of last day in week, CORRECT ORDER'
;

COMMENT ON COLUMN SH.TIMES.CALENDAR_MONTH_NUMBER IS '1 to 12, repeating'
;

COMMENT ON COLUMN SH.TIMES.FISCAL_MONTH_NUMBER IS '1 to 12, repeating'
;

COMMENT ON COLUMN SH.TIMES.CALENDAR_MONTH_DESC IS 'e.g. 1998-01, CORRECT ORDER'
;

COMMENT ON COLUMN SH.TIMES.FISCAL_MONTH_DESC IS 'e.g. 1998-01, CORRECT ORDER'
;

COMMENT ON COLUMN SH.TIMES.DAYS_IN_CAL_MONTH IS 'e.g. 28,31, repeating'
;

COMMENT ON COLUMN SH.TIMES.DAYS_IN_FIS_MONTH IS 'e.g. 25,32, repeating'
;

COMMENT ON COLUMN SH.TIMES.END_OF_CAL_MONTH IS 'last day of calendar month'
;

COMMENT ON COLUMN SH.TIMES.END_OF_FIS_MONTH IS 'last day of fiscal month'
;

COMMENT ON COLUMN SH.TIMES.CALENDAR_MONTH_NAME IS 'January to December, repeating'
;

COMMENT ON COLUMN SH.TIMES.FISCAL_MONTH_NAME IS 'January to December, repeating'
;

COMMENT ON COLUMN SH.TIMES.CALENDAR_QUARTER_DESC IS 'e.g. 1998-Q1, CORRECT ORDER'
;

COMMENT ON COLUMN SH.TIMES.FISCAL_QUARTER_DESC IS 'e.g. 1999-Q3, CORRECT ORDER'
;

COMMENT ON COLUMN SH.TIMES.DAYS_IN_CAL_QUARTER IS 'e.g. 88,90, repeating'
;

COMMENT ON COLUMN SH.TIMES.DAYS_IN_FIS_QUARTER IS 'e.g. 88,90, repeating'
;

COMMENT ON COLUMN SH.TIMES.END_OF_CAL_QUARTER IS 'last day of calendar quarter'
;

COMMENT ON COLUMN SH.TIMES.END_OF_FIS_QUARTER IS 'last day of fiscal quarter'
;

COMMENT ON COLUMN SH.TIMES.CALENDAR_QUARTER_NUMBER IS '1 to 4, repeating'
;

COMMENT ON COLUMN SH.TIMES.FISCAL_QUARTER_NUMBER IS '1 to 4, repeating'
;

COMMENT ON COLUMN SH.TIMES.CALENDAR_YEAR IS 'e.g. 1999, CORRECT ORDER'
;

COMMENT ON COLUMN SH.TIMES.FISCAL_YEAR IS 'e.g. 1999, CORRECT ORDER'
;

COMMENT ON COLUMN SH.TIMES.DAYS_IN_CAL_YEAR IS '365,366 repeating'
;

COMMENT ON COLUMN SH.TIMES.DAYS_IN_FIS_YEAR IS 'e.g. 355,364, repeating'
;

COMMENT ON COLUMN SH.TIMES.END_OF_CAL_YEAR IS 'last day of cal year'
;

COMMENT ON COLUMN SH.TIMES.END_OF_FIS_YEAR IS 'last day of fiscal year'
;

ALTER TABLE SH.TIMES
    ADD CONSTRAINT TIMES_PK PRIMARY KEY ( TIME_ID ) NOVALIDATE ;


CREATE MATERIALIZED VIEW SH.CAL_MONTH_SALES_MV
    USING INDEX
    REFRESH FORCE ON DEMAND WITH ROWID
    ENABLE QUERY REWRITE
    AS SELECT   t.calendar_month_desc
  ,        sum(s.amount_sold) AS dollars
  FROM     sales s
  ,        times t
  WHERE    s.time_id = t.time_id
  GROUP BY t.calendar_month_desc
;


CREATE MATERIALIZED VIEW SH.FWEEK_PSCAT_SALES_MV
    USING INDEX
    REFRESH FORCE ON DEMAND WITH ROWID
    ENABLE QUERY REWRITE
    AS SELECT   t.week_ending_day
  ,        p.prod_subcategory
  ,        sum(s.amount_sold) AS dollars
  ,        s.channel_id
  ,        s.promo_id
  FROM     sales s
  ,        times t
  ,        products p
  WHERE    s.time_id = t.time_id
  AND      s.prod_id = p.prod_id
  GROUP BY t.week_ending_day
  ,        p.prod_subcategory
  ,        s.channel_id
  ,        s.promo_id
;

CREATE BITMAP INDEX SH.FW_PSC_S_MV_SUBCAT_BIX ON SH.FWEEK_PSCAT_SALES_MV
    (
     PROD_SUBCATEGORY ASC
    )
    TABLESPACE EXAMPLE
    PCTFREE 10
    MAXTRANS 255
    STORAGE (
        INITIAL 65536
        NEXT 1048576
        PCTINCREASE 0
        MINEXTENTS 1
        MAXEXTENTS 2147483645
        FREELISTS 1
        FREELIST GROUPS 1
        BUFFER_POOL DEFAULT )
    NOLOGGING
;
CREATE BITMAP INDEX SH.FW_PSC_S_MV_CHAN_BIX ON SH.FWEEK_PSCAT_SALES_MV
    (
     CHANNEL_ID ASC
    )
    TABLESPACE EXAMPLE
    PCTFREE 10
    MAXTRANS 255
    STORAGE (
        INITIAL 65536
        NEXT 1048576
        PCTINCREASE 0
        MINEXTENTS 1
        MAXEXTENTS 2147483645
        FREELISTS 1
        FREELIST GROUPS 1
        BUFFER_POOL DEFAULT )
    NOLOGGING
;
CREATE BITMAP INDEX SH.FW_PSC_S_MV_PROMO_BIX ON SH.FWEEK_PSCAT_SALES_MV
    (
     PROMO_ID ASC
    )
    TABLESPACE EXAMPLE
    PCTFREE 10
    MAXTRANS 255
    STORAGE (
        INITIAL 65536
        NEXT 1048576
        PCTINCREASE 0
        MINEXTENTS 1
        MAXEXTENTS 2147483645
        FREELISTS 1
        FREELIST GROUPS 1
        BUFFER_POOL DEFAULT )
    NOLOGGING
;
CREATE BITMAP INDEX SH.FW_PSC_S_MV_WD_BIX ON SH.FWEEK_PSCAT_SALES_MV
    (
     WEEK_ENDING_DAY ASC
    )
    TABLESPACE EXAMPLE
    PCTFREE 10
    MAXTRANS 255
    STORAGE (
        INITIAL 65536
        NEXT 1048576
        PCTINCREASE 0
        MINEXTENTS 1
        MAXEXTENTS 2147483645
        FREELISTS 1
        FREELIST GROUPS 1
        BUFFER_POOL DEFAULT )
    NOLOGGING
;


ALTER TABLE SH.COSTS
    ADD CONSTRAINT COSTS_CHANNEL_FK FOREIGN KEY
    (
     CHANNEL_ID
    )
    REFERENCES SH.CHANNELS
    (
     CHANNEL_ID
    )
    NOT DEFERRABLE DISABLE NOVALIDATE
;


ALTER TABLE SH.COSTS
    ADD CONSTRAINT COSTS_PRODUCT_FK FOREIGN KEY
    (
     PROD_ID
    )
    REFERENCES SH.PRODUCTS
    (
     PROD_ID
    )
    NOT DEFERRABLE NOVALIDATE
;


ALTER TABLE SH.COSTS
    ADD CONSTRAINT COSTS_PROMO_FK FOREIGN KEY
    (
     PROMO_ID
    )
    REFERENCES SH.PROMOTIONS
    (
     PROMO_ID
    )
    NOT DEFERRABLE DISABLE NOVALIDATE
;


ALTER TABLE SH.COSTS
    ADD CONSTRAINT COSTS_TIME_FK FOREIGN KEY
    (
     TIME_ID
    )
    REFERENCES SH.TIMES
    (
     TIME_ID
    )
    NOT DEFERRABLE NOVALIDATE
;


ALTER TABLE SH.CUSTOMERS
    ADD CONSTRAINT CUSTOMERS_COUNTRY_FK FOREIGN KEY
    (
     COUNTRY_ID
    )
    REFERENCES SH.COUNTRIES
    (
     COUNTRY_ID
    )
    NOT DEFERRABLE NOVALIDATE
;


ALTER TABLE SH.SALES
    ADD CONSTRAINT SALES_CHANNEL_FK FOREIGN KEY
    (
     CHANNEL_ID
    )
    REFERENCES SH.CHANNELS
    (
     CHANNEL_ID
    )
    NOT DEFERRABLE NOVALIDATE
;


ALTER TABLE SH.SALES
    ADD CONSTRAINT SALES_CUSTOMER_FK FOREIGN KEY
    (
     CUST_ID
    )
    REFERENCES SH.CUSTOMERS
    (
     CUST_ID
    )
    NOT DEFERRABLE NOVALIDATE
;


ALTER TABLE SH.SALES
    ADD CONSTRAINT SALES_PRODUCT_FK FOREIGN KEY
    (
     PROD_ID
    )
    REFERENCES SH.PRODUCTS
    (
     PROD_ID
    )
    NOT DEFERRABLE NOVALIDATE
;


ALTER TABLE SH.SALES
    ADD CONSTRAINT SALES_PROMO_FK FOREIGN KEY
    (
     PROMO_ID
    )
    REFERENCES SH.PROMOTIONS
    (
     PROMO_ID
    )
    NOT DEFERRABLE NOVALIDATE
;


ALTER TABLE SH.SALES
    ADD CONSTRAINT SALES_TIME_FK FOREIGN KEY
    (
     TIME_ID
    )
    REFERENCES SH.TIMES
    (
     TIME_ID
    )
    NOT DEFERRABLE NOVALIDATE
;

CREATE DIMENSION SH.CHANNELS_DIM
    LEVEL CHANNEL IS SH.CHANNELS.CHANNEL_ID
    LEVEL CHANNEL_CLASS IS SH.CHANNELS.CHANNEL_CLASS_ID
    LEVEL CHANNEL_TOTAL IS SH.CHANNELS.CHANNEL_TOTAL_ID
    HIERARCHY CHANNEL_ROLLUP (
        CHANNEL  CHILD OF 
        CHANNEL_CLASS  CHILD OF 
        CHANNEL_TOTAL
    )
    ATTRIBUTE CHANNEL LEVEL CHANNEL DETERMINES
        CHANNEL_DESC
    ATTRIBUTE CHANNEL_CLASS LEVEL CHANNEL_CLASS DETERMINES
        CHANNEL_CLASS
    ATTRIBUTE CHANNEL_TOTAL LEVEL CHANNEL_TOTAL DETERMINES
        CHANNEL_TOTAL
;

CREATE DIMENSION SH.CUSTOMERS_DIM
    LEVEL CUST_TOTAL IS SH.CUSTOMERS.CUST_TOTAL_ID
    LEVEL REGION IS SH.COUNTRIES.COUNTRY_REGION_ID
    LEVEL CITY IS SH.CUSTOMERS.CUST_CITY_ID
    LEVEL GEOG_TOTAL IS SH.COUNTRIES.COUNTRY_TOTAL_ID
    LEVEL COUNTRY IS SH.COUNTRIES.COUNTRY_ID
    LEVEL STATE IS SH.CUSTOMERS.CUST_STATE_PROVINCE_ID
    LEVEL CUSTOMER IS SH.CUSTOMERS.CUST_ID
    LEVEL SUBREGION IS SH.COUNTRIES.COUNTRY_SUBREGION_ID
    HIERARCHY CUST_ROLLUP (
        CUSTOMER  CHILD OF 
        CITY  CHILD OF 
        STATE  CHILD OF 
        CUST_TOTAL
    )
    HIERARCHY GEOG_ROLLUP (
        CUSTOMER  CHILD OF 
        CITY  CHILD OF 
        STATE  CHILD OF 
        COUNTRY  CHILD OF 
        SUBREGION  CHILD OF 
        REGION  CHILD OF 
        GEOG_TOTAL
        JOIN KEY CUSTOMERS.COUNTRY_ID REFERENCES COUNTRY
    )
    ATTRIBUTE CUST_TOTAL LEVEL CUST_TOTAL DETERMINES
        CUST_TOTAL
    ATTRIBUTE REGION LEVEL REGION DETERMINES
        COUNTRY_REGION
    ATTRIBUTE CITY LEVEL CITY DETERMINES
        CUST_CITY
    ATTRIBUTE GEOG_TOTAL LEVEL GEOG_TOTAL DETERMINES
        COUNTRY_TOTAL
    ATTRIBUTE COUNTRY LEVEL COUNTRY DETERMINES (
        COUNTRY_NAME ,
        COUNTRY_ISO_CODE
    )
    ATTRIBUTE STATE LEVEL STATE DETERMINES
        CUST_STATE_PROVINCE
    ATTRIBUTE CUSTOMER LEVEL CUSTOMER DETERMINES (
        CUST_FIRST_NAME ,
        CUST_EMAIL ,
        CUST_MAIN_PHONE_NUMBER ,
        CUST_POSTAL_CODE ,
        CUST_STREET_ADDRESS ,
        CUST_CREDIT_LIMIT ,
        CUST_INCOME_LEVEL ,
        CUST_YEAR_OF_BIRTH ,
        CUST_MARITAL_STATUS ,
        CUST_GENDER ,
        CUST_LAST_NAME
    )
    ATTRIBUTE SUBREGION LEVEL SUBREGION DETERMINES
        COUNTRY_SUBREGION
;

CREATE DIMENSION SH.PRODUCTS_DIM
    LEVEL SUBCATEGORY IS SH.PRODUCTS.PROD_SUBCATEGORY_ID
    LEVEL PRODUCT IS SH.PRODUCTS.PROD_ID
    LEVEL PROD_TOTAL IS SH.PRODUCTS.PROD_TOTAL_ID
    LEVEL CATEGORY IS SH.PRODUCTS.PROD_CATEGORY_ID
    HIERARCHY PROD_ROLLUP (
        PRODUCT  CHILD OF 
        SUBCATEGORY  CHILD OF 
        CATEGORY  CHILD OF 
        PROD_TOTAL
    )
    ATTRIBUTE SUBCATEGORY LEVEL SUBCATEGORY DETERMINES (
        PROD_SUBCATEGORY ,
        PROD_SUBCATEGORY_DESC
    )
    ATTRIBUTE PRODUCT LEVEL PRODUCT DETERMINES (
        PROD_NAME ,
        PROD_MIN_PRICE ,
        PROD_LIST_PRICE ,
        PROD_STATUS ,
        PROD_PACK_SIZE ,
        PROD_UNIT_OF_MEASURE ,
        PROD_WEIGHT_CLASS ,
        PROD_DESC
    )
    ATTRIBUTE PROD_TOTAL LEVEL PROD_TOTAL DETERMINES
        PROD_TOTAL
    ATTRIBUTE CATEGORY LEVEL CATEGORY DETERMINES (
        PROD_CATEGORY ,
        PROD_CATEGORY_DESC
    )
;

CREATE DIMENSION SH.PROMOTIONS_DIM
    LEVEL PROMO_TOTAL IS SH.PROMOTIONS.PROMO_TOTAL_ID
    LEVEL CATEGORY IS SH.PROMOTIONS.PROMO_CATEGORY_ID
    LEVEL SUBCATEGORY IS SH.PROMOTIONS.PROMO_SUBCATEGORY_ID
    LEVEL PROMO IS SH.PROMOTIONS.PROMO_ID
    HIERARCHY PROMO_ROLLUP (
        PROMO  CHILD OF 
        SUBCATEGORY  CHILD OF 
        CATEGORY  CHILD OF 
        PROMO_TOTAL
    )
    ATTRIBUTE PROMO_TOTAL LEVEL PROMO_TOTAL DETERMINES
        PROMO_TOTAL
    ATTRIBUTE CATEGORY LEVEL CATEGORY DETERMINES
        PROMO_CATEGORY
    ATTRIBUTE SUBCATEGORY LEVEL SUBCATEGORY DETERMINES
        PROMO_SUBCATEGORY
    ATTRIBUTE PROMO LEVEL PROMO DETERMINES (
        PROMO_NAME ,
        PROMO_END_DATE ,
        PROMO_BEGIN_DATE ,
        PROMO_COST
    )
;

CREATE DIMENSION SH.TIMES_DIM
    LEVEL FIS_MONTH IS SH.TIMES.FISCAL_MONTH_ID
    LEVEL FIS_QUARTER IS SH.TIMES.FISCAL_QUARTER_ID
    LEVEL YEAR IS SH.TIMES.CALENDAR_YEAR_ID
    LEVEL FIS_WEEK IS SH.TIMES.WEEK_ENDING_DAY_ID
    LEVEL QUARTER IS SH.TIMES.CALENDAR_QUARTER_ID
    LEVEL MONTH IS SH.TIMES.CALENDAR_MONTH_ID
    LEVEL DAY IS SH.TIMES.TIME_ID
    LEVEL FIS_YEAR IS SH.TIMES.FISCAL_YEAR_ID
    HIERARCHY CAL_ROLLUP (
        DAY  CHILD OF 
        MONTH  CHILD OF 
        QUARTER  CHILD OF 
        YEAR
    )
    HIERARCHY FIS_ROLLUP (
        DAY  CHILD OF 
        FIS_WEEK  CHILD OF 
        FIS_MONTH  CHILD OF 
        FIS_QUARTER  CHILD OF 
        FIS_YEAR
    )
    ATTRIBUTE FIS_MONTH LEVEL FIS_MONTH DETERMINES (
        FISCAL_MONTH_DESC ,
        END_OF_FIS_MONTH ,
        DAYS_IN_FIS_MONTH ,
        FISCAL_MONTH_NAME ,
        FISCAL_MONTH_NUMBER
    )
    ATTRIBUTE FIS_QUARTER LEVEL FIS_QUARTER DETERMINES (
        FISCAL_QUARTER_DESC ,
        END_OF_FIS_QUARTER ,
        DAYS_IN_FIS_QUARTER ,
        FISCAL_QUARTER_NUMBER
    )
    ATTRIBUTE YEAR LEVEL YEAR DETERMINES (
        CALENDAR_YEAR ,
        END_OF_CAL_YEAR ,
        DAYS_IN_CAL_YEAR
    )
    ATTRIBUTE FIS_WEEK LEVEL FIS_WEEK DETERMINES (
        WEEK_ENDING_DAY ,
        FISCAL_WEEK_NUMBER
    )
    ATTRIBUTE QUARTER LEVEL QUARTER DETERMINES (
        CALENDAR_QUARTER_DESC ,
        END_OF_CAL_QUARTER ,
        DAYS_IN_CAL_QUARTER ,
        CALENDAR_QUARTER_NUMBER
    )
    ATTRIBUTE MONTH LEVEL MONTH DETERMINES (
        CALENDAR_MONTH_DESC ,
        END_OF_CAL_MONTH ,
        DAYS_IN_CAL_MONTH ,
        CALENDAR_MONTH_NAME ,
        CALENDAR_MONTH_NUMBER
    )
    ATTRIBUTE DAY LEVEL DAY DETERMINES (
        DAY_NUMBER_IN_WEEK ,
        CALENDAR_WEEK_NUMBER ,
        DAY_NUMBER_IN_MONTH ,
        DAY_NAME
    )
    ATTRIBUTE FIS_YEAR LEVEL FIS_YEAR DETERMINES (
        FISCAL_YEAR ,
        END_OF_FIS_YEAR ,
        DAYS_IN_FIS_YEAR
    )
;














No comments:

Post a Comment