/*
|| 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