create user demo identified by demo
       default tablespace users
       temporary tablespace temp
       quota unlimited on users;

grant connect, resource, dba to demo;

connect demo/demo

drop table fact purge;

create table fact(time_id date,
                dim2_id number,
                dim3_id1 number,
                dim3_id2 number,
                dim4_id varchar2(15),
                attr1   varchar2(2),
                attr2   varchar2(5),
                attr3   number,
                attr4   date,
                metric1   number,
                metric2   number)
  partition by range(time_id) interval(NUMTOYMINTERVAL(1,'MONTH'))
  subpartition by hash(dim2_id) subpartitions 2
    (partition P0 values less than (to_date('01/01/2009','DD/MM/YYYY')))
  nologging;

insert /*+append */ into fact
  select t.time_id,
         d2.dim2_id,
         d3.dim3_id1,
         d3.dim3_id2,
         d4.dim4_id,
         to_char(mod(t_val,9)),
         'ZZ'||to_char(mod(t_val+d3_val,127)),
         mod(t_val+d3_val+d2_val,1034),
         to_date('01/01/2009','DD/MM/YYYY')
             + mod(t_val+d3_val+d2_val-d4_val,365),
         mod(t_val*d3_val*d2_val+d4_val,2009),
         mod(t_val*d3_val*d2_val*d4_val,40003)
    from (select rownum t_val, 
                 to_date('01/01/2009','DD/MM/YYYY')
                    + floor(rownum/27) time_id
            from dual 
         connect by level <= 10000) t,
         (select rownum d2_val, 
                 rownum dim2_id
            from dual 
         connect by level <= 10) d2,
         (select mod(rownum,7) d3_val, 
                 mod(rownum,3) dim3_id1,
                 floor(rownum/3) dim3_id2
            from dual 
         connect by level <= 30) d3,
         (select rownum d4_val, 
                 'AXIS '||to_char(rownum) dim4_id
            from dual 
         connect by level <= 4) d4;

commit;

exec dbms_stats.gather_table_stats(user, -
                  'FACT', cascade=>true, -
                  method_opt=>'for all columns size 254');

drop table time purge;

create table time (time_id      date,
                   date_lib     varchar2(25),
                   month_id     date,
                   month_lib    varchar2(25),
                   quarter_id   date,
                   quarter_lib  varchar2(25),
                   year_id      date,
                   year_lib     varchar2(25))
   partition by range(time_id) interval(NUMTOYMINTERVAL(1,'MONTH'))
    (partition P0 values less than (to_date('01/01/2009','DD/MM/YYYY')))   
   nologging;

insert /*+ append */ into time
  select time_id,
          to_char(time_id,'YYYYMMDD'),
          trunc(time_id, 'MM'),
          to_char(trunc(time_id, 'MM'),'YYYYMMDD'),
          trunc(time_id, 'Q'),
          to_char(trunc(time_id, 'Q'),'YYYYMM'),
          trunc(time_id, 'YYYY'),
          to_char(trunc(time_id, 'YYYY'),'YYYY')
    from (select to_date('01/01/2009','DD/MM/YYYY')+rownum-1 time_id
            from dual 
         connect by level <= 371) t;

commit;

exec dbms_stats.gather_table_stats(user, -
                  'TIME', cascade=>true, -
                  method_opt=>'for all columns size 254');

create unique index time_pk 
       on time (time_id) local;

alter table time 
      add constraint time_pk 
          primary key (time_id)
          using index time_pk;

drop table dim2 purge;

create table dim2 (dim2_id      number,
                   dim2_lib     varchar2(25),
                   dim2_l1      number,
                   l1_lib       varchar2(25))
   partition by hash(dim2_id) partitions 2
   nologging;

insert /*+ append */ into dim2
         (select rownum, 
                 'Value '||to_char(rownum),
                 mod(rownum ,3),
                 'Aggregate '||to_char(mod(rownum ,3))
            from dual 
         connect by level <= 10);

commit;

exec dbms_stats.gather_table_stats(user, -
                  'DIM2', cascade=>true, -
                  method_opt=>'for all columns size 254');

create unique index dim2_pk 
       on dim2 (dim2_id) local;

alter table dim2 
       add constraint dim2_pk 
           primary key (dim2_id) 
           using index dim2_pk;

drop table dim3 purge;

create table dim3 (dim3_id1      number,
                   dim3_id2      number,
                   dim3_lib      varchar2(25),
                   dim3_l1       number,
                   l1_lib        varchar2(25))
   nologging;

insert /*+ append */ into dim3
         (select mod(rownum,7) d3_val, 
                 mod(rownum,3) dim3_id1,
                 'Value ('||to_char(mod(rownum,7))||','||to_char(mod(rownum,3))||')',
                 floor(rownum/3) dim3_l1,
                 'Aggregate '||to_char(floor(rownum/3))
            from dual 
         connect by level <= 21);
commit;

exec dbms_stats.gather_table_stats(user, -
     'DIM3', cascade=>true,              -
     method_opt=>'for all columns size 254');

create unique index dim3_pk 
       on dim3 (dim3_id1,dim3_id2);

alter table dim3 
      add constraint dim3_pk 
         primary key (dim3_id1,dim3_id2) 
         using index dim3_pk;

drop table dim4 purge;

create table dim4 (dim4_id      varchar2(15),
                   dim4_lib     varchar2(25),
                   dim4_l1      varchar2(15),
                   l1_lib       varchar2(25),
                   dim4_l2      varchar2(15),
                   l2_lib       varchar2(25))
   nologging;


insert /*+ append */ into dim4
         (select 'AXIS '  ||to_char(rownum) dim4_id,
                 'Label ' ||to_char(rownum) dim4_id,
                 'L1.'    ||to_char(case when rownum<3 then 1 
                                        when rownum=3 then 2 
                                        else 3 end),
                 'Level1 '||to_char(case when rownum<3 then 1 
                                        when rownum=3 then 2 
                                        else 3 end),
                 'L2.'    ||to_char(case when rownum<=3 then 1 
                                        else 3 end),
                 'Level2 '||to_char(case when rownum<=3 then 1 
                                        else 3 end)
            from dual 
         connect by level <= 4);


commit;

exec dbms_stats.gather_table_stats(user, -
                  'DIM4', cascade=>true, -
                  method_opt=>'for all columns size 254');

create unique index dim4_pk 
       on dim4 (dim4_id);

alter table dim4 
       add constraint dim4_pk 
           primary key (dim4_id) 
           using index dim4_pk;
drop materialized view ag_fact_by_month;

create materialized view ag_fact_by_month
   partition by range(month_id) interval(NUMTOYMINTERVAL(1,'MONTH'))
   subpartition by hash(dim2_id) subpartitions 2
     (partition p0 values less than (to_date('01/01/2009','DD/MM/YYYY')))
   nologging
   enable query rewrite
   as select t.month_id,
             f.dim2_id,
             f.dim3_id1,
             f.dim3_id2,
             f.dim4_id,
             sum(f.metric1) metric1_sum,
             count(f.metric1) metric1_count,
             avg(f.metric1) metric1_avg,
             sum(f.metric2) metric2_sum,
             count(f.metric2) metric2_count,
             avg(f.metric2) metric2_avg
        from fact f, time t
       where f.time_id=t.time_id
       group by t.month_id, f.dim2_id,
             f.dim3_id1, f.dim3_id2, f.dim4_id;

alter table dim2 modify constraint DIM2_PK rely;
alter table fact add constraint fact_dim2_fk foreign key(dim2_id) references dim2(dim2_id) rely disable;
alter table dim3 modify constraint DIM3_PK rely;
alter table fact add constraint fact_dim3_fk foreign key(dim3_id1,dim3_id2) references dim3(dim3_id1,dim3_id2) rely disable;
alter table dim4 modify constraint DIM4_PK rely;
alter table fact add constraint fact_dim4_fk foreign key(dim4_id) references dim4(dim4_id) rely disable;

create bitmap index fact_time_idx on fact(time_id) local nologging;
create bitmap index fact_dim2_idx on fact(dim2_id) local nologging;
create bitmap index fact_dim3_idx on fact(dim3_id1,dim3_id2) local nologging;
create bitmap index fact_dim4_idx on fact(dim4_id) local nologging;