rmoff

mv_issue_02a.sql

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

prompt 
prompt *****************************
prompt  mv_issue_02a.sql: MV using ANSI joins, re-compiled explicitly
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 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 false */
-- Expected : ORA-12047: PCT FAST REFRESH cannot be used
-- Because the MView is in COMPILATION_ERROR, the only option possible is a complete refresh

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';
Advertisements

Leave a Comment »

No comments yet.

RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Create a free website or blog at WordPress.com.

%d bloggers like this: