mv_issue_01a.sql

/* https://rnm1978.wordpress.com/ */

prompt
prompt *****************************
prompt  mv_issue_01a.sql: interval range partitioned MV, atomic_refresh=false
prompt *****************************
prompt

set linesize 120
set echo off
set timing off
set feedback on

prompt
prompt
prompt 1. Create calendar reference table :
prompt ->

drop table ref_date;

create table ref_date (day_key int, week_key int, month_key int) ;
insert into ref_date select to_char(to_date('2010-11-14','YYYY-MM-DD')+level,'YYYYMMDD') day_key
,to_char(to_date('2010-11-14','YYYY-MM-DD')+level+1-to_char(to_date('2010-11-14','YYYY-MM-DD')+level,'D'),'YYYYMMDD') week_key
,to_char(to_date('2010-11-14','YYYY-MM-DD')+level+1-to_char(to_date('2010-11-14','YYYY-MM-DD')+level,'D'),'YYYYMM') month_key
from dual
connect by level < 29;
commit;


prompt
prompt
prompt 2. Create base fact table :
prompt ->

drop table base_t1;
CREATE TABLE base_t1 ( store_num INTEGER, day_key   INTEGER, item_key  INTEGER, fact_01   INTEGER DEFAULT 0, fact_02   INTEGER DEFAULT 0, fact_03   INTEGER DEFAULT 0)
  PARTITION BY RANGE ( "DAY_KEY") INTERVAL (1) ( PARTITION PART_20101115 VALUES LESS THAN (20101116) NOCOMPRESS)
  PARALLEL;

prompt
prompt
prompt 3. Populate base fact table with data for 2010-11-15 
prompt ->


exec DBMS_RANDOM.SEED('StraussCookPieterson');

insert /* append */ into base_t1 ( store_num , day_key, item_key, fact_01 , fact_02  , fact_03 )
select  trunc(dbms_random.value(0,200)),
	day_key,
	trunc(dbms_random.value(0,30000)),
	dbms_random.value(0,99999999),
	dbms_random.value(0,99999999),
	dbms_random.value(0,99999999)
from ref_date, (select 1 from dual connect by level <= 50)
where day_key = 20101115;
commit;


prompt
prompt
prompt 4. Collect stats on fact table 
prompt ->

exec dbms_stats.gather_table_stats(ownname=>USER,tabname=>'base_t1');


prompt
prompt
prompt 5. Create MV on fact table, aggregating day to week - using interval partitioning 
prompt ->

drop materialized view mv_week;

create materialized view mv_week (week_key, store_num, item_key, fact_01, fact_02, fact_03)
partition by range (week_key) interval (1) (partition part_20101115 values less than (20101116))
build immediate
using no index
refresh force on demand
enable query rewrite
as
select r.week_key
        ,b.store_num
        ,b.item_key
        ,sum(b.fact_01)
        ,sum(b.fact_02)
        ,sum(b.fact_03)
from base_t1 b, ref_date r
where b.day_key = r.day_key
group by r.week_key, b.store_num
        ,b.item_key;

exec dbms_stats.gather_table_stats(ownname=>USER,tabname=>'mv_week');

prompt
prompt MView state after build:
prompt
SELECT LAST_REFRESH_TYPE, to_char(LAST_REFRESH_DATE,'YYYYMMDD-HH24:MI:SS') LAST_REFRESH, STALENESS, COMPILE_STATE, NUM_FRESH_PCT_REGIONS, NUM_STALE_PCT_REGIONS  FROM USER_MVIEWS  WHERE MVIEW_NAME = 'MV_WEEK';

prompt
prompt
prompt 6. Add new partition of data to fact table for week after current data (w/c 2010-11-22) *
prompt ->

insert /* append */ into base_t1 ( store_num , day_key, item_key, fact_01 , fact_02  , fact_03 )
select  trunc(dbms_random.value(0,200)), day_key, trunc(dbms_random.value(0,30000)), dbms_random.value(0,99999999), dbms_random.value(0,99999999), dbms_random.value(0,99999999)
from ref_date, (select 1 from dual connect by level <= 50)
where day_key = (select max(day_key)+7 from base_t1);
 
commit;

prompt
prompt MView state after data added to detail table:
prompt
SELECT LAST_REFRESH_TYPE, to_char(LAST_REFRESH_DATE,'YYYYMMDD-HH24:MI:SS') LAST_REFRESH, STALENESS, COMPILE_STATE, NUM_FRESH_PCT_REGIONS, NUM_STALE_PCT_REGIONS  FROM USER_MVIEWS  WHERE MVIEW_NAME = 'MV_WEEK';

prompt
prompt
prompt 7. Gather stats 
prompt ->

exec dbms_stats.gather_table_stats(ownname=>USER,tabname=>'base_t1');

prompt
prompt Partition status:
prompt
select TABLE_NAME,PARTITIONING_TYPE,SUBPARTITIONING_TYPE, PARTITION_COUNT, STATUS, INTERVAL from user_part_tables where table_name in ('BASE_T1','MV_WEEK');

prompt
prompt
prompt 8. Refresh MV using PCT 
prompt ->
-- Expected: error thrown
-- ORA-12008: error in materialized view refresh path
-- ORA-00936: missing expression
alter session set events '10979 trace name context forever';
EXEC DBMS_MVIEW.refresh('MV_WEEK',method=>'P',ATOMIC_REFRESH=>false);
ALTER SESSION SET EVENTS '10979 trace name context off';

prompt
prompt MView state after attempted refresh:
prompt
SELECT LAST_REFRESH_TYPE, to_char(LAST_REFRESH_DATE,'YYYYMMDD-HH24:MI:SS') LAST_REFRESH, STALENESS, COMPILE_STATE, NUM_FRESH_PCT_REGIONS, NUM_STALE_PCT_REGIONS  FROM USER_MVIEWS  WHERE MVIEW_NAME = 'MV_WEEK';