/* 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';
mv_issue_02a.sql
Leave a Comment »
No comments yet.
Leave a comment