set linesize 130 pagesize 0

drop table fct_sales;
drop table dim_month_of_sale;
drop table dim_location;

create table dim_month_of_sale
   (
   month_of_sale date primary key,
   fiscal_month  char(7) not null unique,
   fiscal_year   char(4) not null
   )
tablespace test
/
-- add twelve month records
insert into dim_month_of_sale
select add_months(date '2005-10-01',rownum-1),
       to_char(add_months(date '2005-10-01',rownum+2),'YYYY MM'),
       to_char(add_months(date '2005-10-01',rownum+2),'YYYY')
from   dual
connect by level <= 12
/

create table dim_location
   (
   location_cd number primary key,
   region      varchar2(8) not null
   )
tablespace test
/
insert into dim_location
select rownum,
       'Region '||to_char(mod(rownum,3)+1,'fm9')
from   dual
connect by level <= 12
/

/*
|| Create RANGE partitioned fact table
*/
create table fct_sales
   (
   month_of_sale   date        not null
                              references dim_month_of_sale,
   location_cd    number      not null
                              references dim_location,
   transaction_id number      not null,
   sale_amt       number      not null
   )
pctfree 0 nologging
tablespace test
partition by range(month_of_sale)
(
partition Y2005M10 values less than (date '2005-11-01'),
partition Y2005M11 values less than (date '2005-12-01'),
partition Y2005M12 values less than (date '2006-01-01'),
partition Y2006M01 values less than (date '2006-02-01'),
partition Y2006M02 values less than (date '2006-03-01'),
partition Y2006M03 values less than (date '2006-04-01'),
partition Y2006M04 values less than (date '2006-05-01'),
partition Y2006M05 values less than (date '2006-06-01'),
partition Y2006M06 values less than (date '2006-07-01'),
partition Y2006M07 values less than (date '2006-08-01'),
partition Y2006M08 values less than (date '2006-09-01'),
partition Y2006M09 values less than (date '2006-10-01'))
/

/*
|| Populate fact table with 12000 rows per partition
*/

insert /*+ append */
       into fct_sales
       (
       month_of_sale,
       location_cd,
       transaction_id,
       sale_amt
       )
select month_of_sale,
       location_cd,
       rownum,
       dbms_random.value(0.01,100)
from   dim_month_of_sale,
       dim_location,
       (select 1 from dual connect by level <= 1000)
/
commit
/


begin
   dbms_stats.gather_table_stats(
       ownname => user,
       tabname => 'fct_sales',
       method_opt=> 'for columns location_cd size 254',
       granularity => 'ALL');
   dbms_stats.gather_table_stats(
       ownname => user,
       tabname => 'dim_month_of_sale',
       method_opt=> 'for all columns size 254');
   dbms_stats.gather_table_stats(
       ownname => user,
       tabname => 'dim_location',
       method_opt=> 'for all columns size 254');
end;
/

-- Check that no column statistics are in place for MONTH_OF_SALE
select   column_name,
         count(low_value),
         count(*)
from     user_part_col_statistics
where    table_name = 'FCT_SALES'
group by column_name
/

explain plan for
select   *
from     fct_sales fs,
         dim_month_of_sale dm
where    fs.month_of_sale = dm.month_of_sale and
         dm.month_of_sale = date '2006-01-01'
/

select * from table(dbms_xplan.display())
/

-- Gather column statistics for the partition key

begin
   dbms_stats.gather_table_stats(
       ownname => user,
       tabname => 'fct_sales',
       method_opt=> 'for columns location_cd size 254, month_of_sale size 1',
       granularity => 'ALL');
end;
/

explain plan for
select   *
from     fct_sales fs,
         dim_month_of_sale dm
where    fs.month_of_sale = dm.month_of_sale and
         dm.month_of_sale = date '2006-01-01'
/

select * from table(dbms_xplan.display())
/


drop table fct_sales
/

/*
|| Create LIST partitioned fact table
*/
create table fct_sales
   (
   month_of_sale   date        not null
                              references dim_month_of_sale,
   location_cd    number      not null
                              references dim_location,
   transaction_id number      not null,
   sale_amt       number      not null
   )
pctfree 0 nologging
tablespace test
partition by list (month_of_sale)
(
partition Y2005M10 values (date '2005-10-01'),
partition Y2005M11 values (date '2005-11-01'),
partition Y2005M12 values (date '2005-12-01'),
partition Y2006M01 values (date '2006-01-01'),
partition Y2006M02 values (date '2006-02-01'),
partition Y2006M03 values (date '2006-03-01'),
partition Y2006M04 values (date '2006-04-01'),
partition Y2006M05 values (date '2006-05-01'),
partition Y2006M06 values (date '2006-06-01'),
partition Y2006M07 values (date '2006-07-01'),
partition Y2006M08 values (date '2006-08-01'),
partition Y2006M09 values (date '2006-09-01'))
/

/*
|| Populate fact table with 12000 rows per partition
*/

insert /*+ append */
       into fct_sales
       (
       month_of_sale,
       location_cd,
       transaction_id,
       sale_amt
       )
select month_of_sale,
       location_cd,
       rownum,
       dbms_random.value(0.01,100)
from   dim_month_of_sale,
       dim_location,
       (select 1 from dual connect by level <= 1000)
/
commit
/
begin
   dbms_stats.gather_table_stats(
       ownname => user,
       tabname => 'fct_sales',
       method_opt=> 'for columns location_cd size 254',
       granularity => 'ALL');
end;
/

-- Check that no column statistics are in place for MONTH_OF_SALE
select   column_name,
         count(low_value),
         count(*)
from     user_part_col_statistics
where    table_name = 'FCT_SALES'
group by column_name
/

explain plan for
select   *
from     fct_sales fs,
         dim_month_of_sale dm
where    fs.month_of_sale = dm.month_of_sale and
         dm.month_of_sale = date '2006-01-01'
/

select * from table(dbms_xplan.display())
/

-- Gather column statistics for the partition key

begin
   dbms_stats.gather_table_stats(
       ownname => user,
       tabname => 'fct_sales',
       method_opt=> 'for columns location_cd size 254, month_of_sale size 1',
       granularity => 'ALL');
end;
/

explain plan for
select   *
from     fct_sales fs,
         dim_month_of_sale dm
where    fs.month_of_sale = dm.month_of_sale and
         dm.month_of_sale = date '2006-01-01'
/

select * from table(dbms_xplan.display())
/