Partitioning and Materialized Views

Having a fact table that is partitioned offers two additional benefits for materialized views. The fact that only some partitions have changed, due to a DML or a partition maintenance operation, is useful for:

• Database Oracle - Query Rewrite: As long as no stale area of the materialized view is touched, it can be used for rewrite.
• Refresh: The partition information is used to improve refresh of a materialized view

Partitioning and Query Rewrite
When a certain partition of the detail table is updated, only specific sections of the materialized view are marked stale. The materialized view must have information that can identify the partition of the table corresponding to a particular row or group of the materialized view. The simplest scenario is when the partitioning key of the table is available in the SELECT list of the materialized view, because this is the easiest way to map a row to a stale partition. The key points when using partially stale materialized views are:

• Query rewrite can use an materialized view in ENFORCED or TRUSTED mode if the rows from the materialized view used to answer the query are known to be FRESH.
• The fresh rows in the materialized view are identified by adding selection predicates to the materialized view's WHERE clause. You rewrite a query with this materialized view if its answer is contained within this (restricted) materialized view. Note that support for materialized views with selection predicates is a prerequisite for this type of rewrite.

Partitioning and Refresh
In a data warehouse, changes to the detail tables can often entail partition maintenance operations, such as DROP, EXCHANGE, MERGE, and ADD PARTITION. To maintain the materialized view after such operations in Oracle8i required the use of manual maintenance (see also CONSIDER FRESH) or complete refresh. Beginning with Oracle9i, an addition to fast refresh known as Partition Change Tracking (PCT) refresh, was added.

For PCT to be available, the detail tables must be partitioned. The partitioning of the materialized view itself has no bearing on this feature. If PCT refresh is possible, it will occur automatically and no user intervention is required in order for it to occur.

DROP MATERIALIZED VIEW costs_mv;
 
CREATE MATERIALIZED VIEW costs_mv
   BUILD IMMEDIATE
   REFRESH FAST ON DEMAND
   ENABLE QUERY REWRITE 
   AS
   SELECT time_id, prod_name, SUM( unit_cost) AS sum_units,
          COUNT(unit_cost) AS count_units, COUNT(*) AS cnt
   FROM   costs c, products p
   WHERE  c.prod_id = p.prod_id
   GROUP BY time_id, prod_name;

The materialized view is FRESH.

ALTER MATERIALIZED VIEW costs_mv COMPILE;
 
SELECT mview_name, refresh_mode, refresh_method, staleness 
FROM   user_mviews;

Output :

MVIEW_NAME                     REFRES REFRESH_ STALENESS
------------------------------ ------ -------- -------------------
CUST_SALES_MV                  COMMIT FAST     FRESH
SALES_CUBE_MV                  DEMAND FORCE    FRESH
SALES_GBY_MV                   DEMAND FORCE    FRESH
COSTS_MV                       DEMAND FAST     FRESH

You can ignore any ORA-2149 and ORA-12002 SQL errors.

TRUNCATE TABLE mv_capabilities_table;
 
EXEC DBMS_MVIEW.EXPLAIN_MVIEW('costs_mv');
 
SET SERVEROUTPUT ON
 
BEGIN
   FOR crec IN (SELECT capability_name, possible, related_text, msgtxt 
                FROM mv_capabilities_table ORDER BY 1) LOOP
      DBMS_OUTPUT.PUT_LINE(crec.capability_name ||': '||crec.possible);
      DBMS_OUTPUT.PUT_LINE(crec.related_text||': '||crec.msgtxt);
   END LOOP;
END;
/

Output :

PCT: Y
:
PCT_TABLE: N
PRODUCTS: relation is not a partitioned table
PCT_TABLE: Y
COSTS:
PCT_TABLE_REWRITE: N
PRODUCTS: relation is not a partitioned table
PCT_TABLE_REWRITE: Y
COSTS:
REFRESH_COMPLETE: Y
:
REFRESH_FAST: Y
:
REFRESH_FAST_AFTER_ANY_DML: Y
:
REFRESH_FAST_AFTER_INSERT: Y
:
REFRESH_FAST_AFTER_ONETAB_DML: Y
:
REFRESH_FAST_PCT: Y
:
REWRITE: Y
:
REWRITE_FULL_TEXT_MATCH: Y
:
REWRITE_GENERAL: Y
:
REWRITE_PARTIAL_TEXT_MATCH: Y
:
REWRITE_PCT: Y
:

PL/SQL procedure successfully completed.

You can see that Partition Change Tracking (PCT) is enabled for the COSTS table and for query rewrite.

ALTER TABLE costs
   ADD PARTITION costs_q1_2002 
   VALUES less than (TO_DATE('01-APR-2002', 'DD-MON-YYYY'));
 
ALTER TABLE costs 
   ADD PARTITION costs_q2_2002 
   VALUES less than (TO_DATE('01-JUL-2002', 'DD-MON-YYYY'));
 
Rem The materialized VIEW IS still FRESH.
 
INSERT INTO costs VALUES (20, '02-JAN-02',999, 99, 2.50, 25.00);
INSERT INTO costs VALUES (30, '02-FEB-02',999, 99, 2, 34);
INSERT INTO costs VALUES (30, '03-MAR-02',999, 99, 2, 34);
INSERT INTO costs VALUES (40, '21-APR-02',999, 99, 1, 35);
INSERT INTO costs VALUES (40, '22-MAY-02',999, 99, 3, 36);
INSERT INTO costs VALUES (30, '22-APR-02',999, 99, 4, 37);
INSERT INTO costs VALUES (20, '12-JUN-02',999, 99, 5, 34);
 
COMMIT;
 
ALTER MATERIALIZED VIEW costs_mv COMPILE;
 
SELECT mview_name, refresh_mode, refresh_method, staleness 
FROM   user_mviews
WHERE  mview_name LIKE 'COST%';

Output :

MVIEW_NAME                     REFRES REFRESH_ STALENESS
------------------------------ ------ -------- -------------------
COSTS_MV                       DEMAND FAST     STALE

SELECT SUM(sum_units) 
FROM   costs_mv;

Output :

SUM(SUM_UNITS)
--------------
    9219236.75

Fast Refresh :

Rem Now do a fast refresh
EXEC dbms_mview.refresh('costs_mv','F');

Compare the refresh time to the creation time of the materialized view. Because you only have to refresh the costs_q1_2002and costs_q2_2002 partitions, the refresh needs a fraction of the time of its initial creation. The initial creation time is the time it would need for a complete refresh.

Check the sum again

SELECT  SUM(sum_units) 
FROM    costs_mv;

Output :

SUM(SUM_UNITS)
--------------
    9219256.25

The materialized view is obviously fresh again.

To continue : http://www.oracle.com/technology/obe/obe10gdb/bidw/mv/mv.htm#t5b

To continue : http://www.oracle.com/technology/obe/obe10gdb/bidw/mv/mv.htm#t5b

To continue : http://www.oracle.com/technology/obe/obe10gdb/bidw/mv/mv.htm#t5b