mv_issue_02b.sql

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

prompt
prompt *****************************
prompt  mv_issue_02b.sql: MV using ANSI joins, re-compiled implicitly
prompt *****************************
prompt

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

/* 1. Create calendar reference table */

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;

/* 2. Create base fact table */

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;

/* 3. Populate base fact table with data for w/c 2010-11-15 */

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;


/* 4. Collect stats on fact table */

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


/* 5. Create MV on fact table, aggregating day to week */
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) (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 inner join  ref_date r on 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: 
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';

/* 6. Add new partition of data to fact table for week after current data (w/c 2010-11-22) */
Prompt Adding new data to detail table
 
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 change on detail table
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 Select a row from detail table, this causes implicit compilation of the MV
select * from base_t1 WHERE ROWNUM=1;

prompt 
prompt MView state after implicit compilation:
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';

/* 7. Add new partition to the MV for the new data*/
alter table mv_week add partition part_20101122 values less than (20101123);

prompt 
prompt MView state after adding empty partition for new data
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';

/* 9. Refresh MV using PCT, atomic_refresh=>false */
-- Expected : ORA-12047: PCT FAST REFRESH cannot be used
-- Why?

prompt 
prompt Now refresh the MView:
EXEC DBMS_MVIEW.refresh('MV_WEEK',method=>'P',ATOMIC_REFRESH=>false);

prompt
prompt MView state after refresh attempt
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';