/*
|| Oracle 10gR2 Materialized View Enhancements Listing 2
||
|| Demonstrates new Oracle 10gR2 Materialized View (MV) features, including:
|| - Examples of Partition Change Tracking (PCT) for MVs
|| - How TRUNCATE PARTITION affects an MV that references a partitioned table
|| - Using Partition Maintenance Operations (PMOPs) with MVs
||
|| Author: Jim Czuprynski
||
|| Usage Notes:
|| This script is provided to demonstrate various features of Oracle 10g
|| materialized view enhancements, and it should be carefully proofread before
|| executing it against any existing Oracle database to insure that no
|| potential damage can occur.
||
*/

/*  
|| Listing 2.1: Create a new table for PCT demonstrations. It will contain
||              sales history data copied from SH.SALES, list-partitioned
||              across specific month numbers into four quarters of data
*/

DROP TABLE sh.list_sales_time PURGE;
CREATE table sh.list_sales_time (
     time_id
    ,cust_id
    ,month
    ,week
    ,quantity_sold
    ,amount_sold
)
    PARTITION BY LIST (month)(
        PARTITION q1 
            VALUES ( 1,  2,  3),
        PARTITION q2 
            VALUES ( 4,  5,  6),
        PARTITION q3 
            VALUES ( 7,  8,  9),
        PARTITION q4 
            VALUES (10, 11, 12),
        PARTITION others 
            VALUES (DEFAULT))
AS
SELECT 
     S.time_id
    ,S.cust_id
    ,T.fiscal_month_number
    ,T.fiscal_week_number
    ,S.quantity_sold
    ,S.amount_sold
  FROM 
     sh.sales S
    ,sh.times T
 WHERE S.time_id = T.time_id 
   AND S.promo_id < 2000
   AND T.calendar_year = 2000
;
-----
-- Create a new materialized view based on the new table. Note
-- that a materialized view log is also created to enable PCT
-- features!
-----
DROP MATERIALIZED VIEW LOG ON sh.list_sales_time;
CREATE MATERIALIZED VIEW LOG 
    ON sh.list_sales_time WITH ROWID
    (month)
    INCLUDING NEW VALUES;

DROP MATERIALIZED VIEW sh.mv_pct_sales;
CREATE MATERIALIZED VIEW sh.mv_pct_sales
    BUILD IMMEDIATE --DEFERRED
    REFRESH FAST ON COMMIT --DEMAND
    ENABLE QUERY REWRITE
AS
SELECT 
     cust_id
    ,month
    ,SUM(quantity_sold) as tot_qty_sold
    ,AVG(quantity_sold) AS avg_qty_sold
    ,COUNT(quantity_sold) AS cnt_qty_sold
    ,SUM(amount_sold) as tot_amt_sold
    ,AVG(amount_sold) AS avg_amt_sold
    ,COUNT(amount_sold) AS cnt_amt_sold
  FROM sh.list_sales_time LS
 GROUP BY 
     cust_id
    ,month
;

/*  
|| Listing 2.2: Demonstrate applying updates to the SH.LIST_SALES_TIME table
||              and their impact on the underlying materialized view
*/

-----
-- Now update just one part of the underlying table ...
-----
SELECT /*+NOREWRITE*/
     MONTH
    ,SUM(quantity_sold)
    ,SUM(amount_sold)
  FROM sh.list_sales_time
 WHERE month IN (1,2,3)
 GROUP BY ROLLUP(month);
;

UPDATE sh.list_sales_time
   SET 
     quantity_sold = quantity_sold * 0.80
     ,amount_sold = amount_sold * 1.20
 WHERE month IN (1,2,3);

COMMIT; 
   
SELECT /*+NOREWRITE*/
     MONTH
    ,SUM(quantity_sold)
    ,SUM(amount_sold)
  FROM sh.list_sales_time
 WHERE month IN (1,2,3)
 GROUP BY ROLLUP(month);
;

-----
-- ... then force the refresh of a PCT-enabled MV with DBMS_MVIEW.REFRESH(), 
-- letting Oracle choose the most appropriate refresh method. This should
-- be the (P)artitioned refresh method (FAST_PCT), and Oracle can be forced
-- to use it by specifying (P) for the method in DBMS_MVIEW.REFRESH.
-----
BEGIN
    DBMS_MVIEW.REFRESH(
         list => 'sh.mv_pct_sales'
        ,method => '?'
    );
END;
/

/*  
|| Listing 2.3: Queries for ascertaining the status of selected
||              materialized views for the SH schema
*/

TTITLE 'Status of SH-Owned Materialized Views|(From DBA_MVIEWS)'
COL owner               FORMAT A05      HEADING 'Owner'           
COL mview_name          FORMAT A24      HEADING 'Materialized View'
COL rewrite_enabled     FORMAT A04      HEADING 'Qry|Rwr|Enb?'
COL refresh_mode        FORMAT A07      HEADING 'Refresh|Mode'
COL refresh_method      FORMAT A07      HEADING 'Refresh|Method'
COL fast_refreshable    FORMAT A18      HEADING 'Fast|Refresh|-able?'
COL refreshed_on        FORMAT A10      HEADING 'Last|Refreshed|On' WRAP  
COL last_refresh_type   FORMAT A08      HEADING 'Last|Refresh|Type'
COL staleness           FORMAT A07      HEADING 'Stale?'
COL after_fast_refresh  FORMAT A07      HEADING 'After|Fast|Refresh' 
SELECT
     owner
    ,mview_name
    ,rewrite_enabled
    ,refresh_mode
    ,refresh_method
    ,fast_refreshable
    ,TO_CHAR(last_refresh_date, 'mm-dd-yyyy hh24:mi:ss') refreshed_on
    ,last_refresh_type
    ,staleness
    ,after_fast_refresh
  FROM dba_mviews
 WHERE owner = 'SH'
 ORDER BY last_refresh_date DESC
;
TTITLE OFF

TTITLE 'Analysis of SH-Owned Materialized Views|(From DBA_MVIEW_ANALYSIS)'
COL owner               FORMAT A05      HEADING 'Owner'           
COL mview_name          FORMAT A24      HEADING 'Materialized View'
COL refreshed_on        FORMAT A20      HEADING 'Last Refreshed On'
COL refresh_method      FORMAT A07      HEADING 'Refresh|Method'
COL summary             FORMAT A01      HEADING 'S|M|Y|?'
COL unusable            FORMAT A01      HEADING 'U|N|U|?'
COL invalid             FORMAT A01      HEADING 'I|N|V|?'
COL rewrite_enabled     FORMAT A01      HEADING 'R|W|R|?'
SELECT
     owner
    ,mview_name
    ,TO_CHAR(last_refresh_date, 'mm-dd-yyyy hh24:mi:ss') refreshed_on
    ,refresh_method
    ,summary
    ,unusable
    ,invalid
    ,rewrite_enabled
  FROM dba_mview_analysis
 WHERE owner = 'SH'
 ORDER BY last_refresh_date DESC
;
TTITLE OFF

TTITLE 'Last Refreshes of SH-Owned Materialized Views|(From DBA_MVIEW_REFRESH_TIMES)'
COL owner               FORMAT A05      HEADING 'Owner'           
COL name                FORMAT A24      HEADING 'Materialized View'
COL master              FORMAT A24      HEADING 'Master View'
COL refreshed_on        FORMAT A20      HEADING 'Last Refreshed On'
SELECT
     owner
    ,name
    ,master
    ,TO_CHAR(last_refresh, 'mm-dd-yyyy hh24:mi:ss') refreshed_on
  FROM dba_mview_refresh_times
 WHERE owner = 'SH'
 ORDER BY last_refresh DESC
;
TTITLE OFF
>>> Results of queries after FAST_PCT refresh operation:

Fri Sep 22                                                                                           page    1

                                    Status of SH-Owned Materialized Views
                                              (From DBA_MVIEWS)

                               Qry                  Fast               Last       Last           
                               Rwr  Refresh Refresh Refresh            Refreshed  Refresh
Owner Materialized View        Enb? Mode    Method  -able?             On         Type     Stale?
----- ------------------------ ---- ------- ------- ------------------ ---------- -------- -------
SH    MV_PCT_SALES             Y    COMMIT  FAST    DIRLOAD_LIMITEDDML 09-22-2006 FAST_PCT FRESH
                                                                        11:54:51

SH    MV_CUST_MTH_SALES        Y    DEMAND  FAST    DIRLOAD_DML        09-22-2006 COMPLETE FRESH
                                                                        10:31:38

SH    MJV_CURRENT_SALES_FORCE  Y    COMMIT  FAST    DIRLOAD_DML        09-16-2006 COMPLETE UNKNOWN
                                                                        15:37:04

SH    FWEEK_PSCAT_SALES_MV     Y    DEMAND  FORCE   DIRLOAD_LIMITEDDML 12-30-2005 NA       UNKNOWN
                                                                        23:32:58

SH    CAL_MONTH_SALES_MV       Y    DEMAND  FORCE   DIRLOAD_LIMITEDDML 12-30-2005 NA       UNKNOWN
                                                                        23:32:55


Fri Sep 22                                                                                           page    1

                                   Analysis of SH-Owned Materialized Views
                                          (From DBA_MVIEW_ANALYSIS)

                                                            S U I R
                                                            M N N W
                                                    Refresh Y U V R
Owner Materialized View        Last Refreshed On    Method  ? ? ? ?
----- ------------------------ -------------------- ------- - - - -
SH    MV_PCT_SALES             09-22-2006 11:54:51  FAST    Y N N Y
SH    MV_CUST_MTH_SALES        09-22-2006 10:31:38  FAST    Y N N Y
SH    MJV_CURRENT_SALES_FORCE  09-16-2006 15:37:04  FAST    N N N Y
SH    FWEEK_PSCAT_SALES_MV     12-30-2005 23:32:58  FORCE   Y N N Y
SH    CAL_MONTH_SALES_MV       12-30-2005 23:32:55  FORCE   Y N N Y


Fri Sep 22                                                                                           page    1

                                Last Refreshes of SH-Owned Materialized Views
                                        (From DBA_MVIEW_REFRESH_TIMES)

Owner Materialized View        Master View              Last Refreshed On
----- ------------------------ ------------------------ --------------------
SH    MV_PCT_SALES             LIST_SALES_TIME          09-22-2006 11:54:43
SH    MV_CUST_MTH_SALES        TIMES                    09-22-2006 10:31:38
SH    MV_CUST_MTH_SALES        PRODUCTS                 09-22-2006 10:31:38
SH    MV_CUST_MTH_SALES        SALES                    09-22-2006 10:31:37
SH    MJV_CURRENT_SALES_FORCE  EMPLOYEES                09-16-2006 15:37:03
SH    MJV_CURRENT_SALES_FORCE  SALES_AREA_HIERARCHIES   09-16-2006 15:37:03
SH    MJV_CURRENT_SALES_FORCE  SALES_AREAS              09-16-2006 15:37:03
SH    MJV_CURRENT_SALES_FORCE  SALES_FORCE_ASSIGNMENTS  09-16-2006 15:37:03
SH    FWEEK_PSCAT_SALES_MV     PRODUCTS                 12-30-2005 23:32:58
SH    FWEEK_PSCAT_SALES_MV     TIMES                    12-30-2005 23:32:58
SH    FWEEK_PSCAT_SALES_MV     SALES                    12-30-2005 23:32:58
SH    CAL_MONTH_SALES_MV       TIMES                    12-30-2005 23:32:53
SH    CAL_MONTH_SALES_MV       SALES                    12-30-2005 23:32:52


/*  
|| Listing 2.4: Create a materialized view with an underlying table
||              that is partitioned to demonstrate Partition Maintenance
||              Operations (PMOPs)
*/

-----
-- Create a new materialized view based on table SH.LIST_SALES_TIME.
-- This materialized view (SH.MV_PCT_PART_SALES) is partitioned on 
-- the same key as the table, but is a range partition instead of 
-- a list partition.
-----
DROP MATERIALIZED VIEW sh.mv_pct_part_sales;
CREATE MATERIALIZED VIEW sh.mv_pct_part_sales
    PARTITION BY RANGE (month) 
    (
        PARTITION per1
            VALUES LESS THAN ( 4)
            TABLESPACE example
       ,PARTITION per2
            VALUES LESS THAN ( 7)
            TABLESPACE example
       ,PARTITION per3
            VALUES LESS THAN (10)
            TABLESPACE example
       ,PARTITION per4
            VALUES LESS THAN (13)
            TABLESPACE example
    )
    BUILD IMMEDIATE
    REFRESH FAST ON COMMIT
    ENABLE QUERY REWRITE
AS
SELECT 
     cust_id
    ,month
    ,SUM(quantity_sold) as tot_qty_sold
    ,AVG(quantity_sold) AS avg_qty_sold
    ,COUNT(quantity_sold) AS cnt_qty_sold
    ,SUM(amount_sold) as tot_amt_sold
    ,AVG(amount_sold) AS avg_amt_sold
    ,COUNT(amount_sold) AS cnt_amt_sold
  FROM sh.list_sales_time LS
 GROUP BY 
     cust_id
    ,month
;

-----
-- Example 2.4.1: Results of DROPping a partition
-----

-- Drop the Q1 partition from the SH.LIST_SALES_TIME table ...
ALTER TABLE sh.list_sales_time
    DROP PARTITION q1;

-- ... and note that it's immediately reflected in the 
-- corresponding materialized views.
COL month           FORMAT 999          HEADING 'Mth'
COL qty_sold        FORMAT 999,999,999  HEADING 'Qty|Sold'
COL amt_sold        FORMAT 999,999,999  HEADING 'Amt|Sold'
TTITLE 'Test 1: After Dropping a Partition|(From SH.LIST_SALES_TIME)'
SELECT 
     MONTH
    ,SUM(quantity_sold) qty_sold
    ,SUM(amount_sold)   amt_sold
  FROM sh.list_sales_time
 GROUP BY ROLLUP(month)
;
TTITLE OFF

       Test 1: After Dropping a Partition
       (From SH.LIST_SALES_TIME)

              Qty          Amt
 Mth         Sold         Sold
---- ------------ ------------
   4       22,389    2,168,753
   5       16,795    1,522,048
   6       18,770    1,687,211
   7       20,841    2,291,922
   8       17,127    1,693,986
   9       19,759    2,087,784
  10       21,996    2,268,190
  11       21,658    2,172,534
  12       16,022    2,291,432
          175,357   18,183,860

TTITLE 'Test 1: After Dropping a Partition|(From SH.MV_PCT_SALES)'
SELECT  
     MONTH
    ,SUM(tot_qty_sold)  qty_sold
    ,SUM(tot_amt_sold)  amt_sold
  FROM sh.mv_pct_sales
 GROUP BY ROLLUP(month)
;
TTITLE OFF

       Test 1: After Dropping a Partition
       (From SH.MV_PCT_SALES)

              Qty          Amt
 Mth         Sold         Sold
---- ------------ ------------
   4       22,389    2,168,753
   5       16,795    1,522,048
   6       18,770    1,687,211
   7       20,841    2,291,922
   8       17,127    1,693,986
   9       19,759    2,087,784
  10       21,996    2,268,190
  11       21,658    2,172,534
  12       16,022    2,291,432
          175,357   18,183,860

TTITLE 'Test 1: After Dropping a Partition|(From SH.MV_PCT_PART_SALES)'
SELECT 
     MONTH
    ,SUM(tot_qty_sold)  qty_sold
    ,SUM(tot_amt_sold)  amt_sold
  FROM sh.mv_pct_part_sales
 GROUP BY ROLLUP(month)
;
TTITLE OFF

Test 1: After Dropping a Partition
       (From SH.MV_PCT_PART_SALES)

              Qty          Amt
 Mth         Sold         Sold
---- ------------ ------------
   4       22,389    2,168,753
   5       16,795    1,522,048
   6       18,770    1,687,211
   7       20,841    2,291,922
   8       17,127    1,693,986
   9       19,759    2,087,784
  10       21,996    2,268,190
  11       21,658    2,172,534
  12       16,022    2,291,432
          175,357   18,183,860

-----
-- Example 2.4.2: Results of TRUNCATing a partition
-----
-- Truncate the Q4 partition from the SH.LIST_SALES_TIME table ...
ALTER TABLE sh.list_sales_time
    TRUNCATE PARTITION q4;

-- ... and again, note that it's immediately reflected in the 
-- corresponding materialized views.
COL month           FORMAT 999          HEADING 'Mth'
COL qty_sold        FORMAT 999,999,999  HEADING 'Qty|Sold'
COL amt_sold        FORMAT 999,999,999  HEADING 'Amt|Sold'
TTITLE 'Test 2: After Truncating a Partition|(From SH.LIST_SALES_TIME)'
SELECT 
     MONTH
    ,SUM(quantity_sold) qty_sold
    ,SUM(amount_sold)   amt_sold
  FROM sh.list_sales_time
 GROUP BY ROLLUP(month)
;
TTITLE OFF

    Test 2: After Truncating a Partition
        (From SH.LIST_SALES_TIME)

              Qty          Amt
 Mth         Sold         Sold
---- ------------ ------------
   4       22,389    2,168,753
   5       16,795    1,522,048
   6       18,770    1,687,211
   7       20,841    2,291,922
   8       17,127    1,693,986
   9       19,759    2,087,784
          115,681   11,451,704

TTITLE 'Test 2: After Truncating a Partition|(From SH.MV_PCT_SALES)'
SELECT  
     MONTH
    ,SUM(tot_qty_sold)  qty_sold
    ,SUM(tot_amt_sold)  amt_sold
  FROM sh.mv_pct_sales
 GROUP BY ROLLUP(month)
;
TTITLE OFF
    Test 2: After Truncating a Partition
        (From SH.MV_PCT_SALES)

              Qty          Amt
 Mth         Sold         Sold
---- ------------ ------------
   4       22,389    2,168,753
   5       16,795    1,522,048
   6       18,770    1,687,211
   7       20,841    2,291,922
   8       17,127    1,693,986
   9       19,759    2,087,784
          115,681   11,451,704

TTITLE 'Test 2: After Truncating a Partition|(From SH.MV_PCT_PART_SALES)'
SELECT 
     MONTH
    ,SUM(tot_qty_sold)  qty_sold
    ,SUM(tot_amt_sold)  amt_sold
  FROM sh.mv_pct_part_sales
 GROUP BY ROLLUP(month)
;
TTITLE OFF

    Test 2: After Truncating a Partition
        (From SH.MV_PCT_PART_SALES)

              Qty          Amt
 Mth         Sold         Sold
---- ------------ ------------
   4       22,389    2,168,753
   5       16,795    1,522,048
   6       18,770    1,687,211
   7       20,841    2,291,922
   8       17,127    1,693,986
   9       19,759    2,087,784
          115,681   11,451,704