Refreshing Materialized Views

Refreshing Materialized Views

Performing a refresh operation requires temporary space to rebuild the indexes and can require additional space for performing the refresh operation itself.

You can temporarily disable query rewrite with an

ALTER SYSTEM SET QUERY_REWRITE_ENABLED = FALSE statement.

After refreshing the materialized views, you can re-enable query rewrite as the default for all sessions in the current database instance by specifying ALTER SYSTEM SET QUERY_REWRITE_ENABLED as TRUE.

Refresh Types for Materialized Views

Ø  The refresh method can be incremental (Fast) or a complete refresh.

Ø  There are two incremental refresh methods, known as log-based refresh and partition change tracking (PCT) refresh.

Complete Refresh

Ø  A complete refresh occurs when the materialized view is initially defined as BUILD IMMEDIATE, unless the materialized view references a prebuilt table

v  The out-of-place refresh option works with all existing refresh methods, such as FAST ('F'), COMPLETE ('C'), PCT ('P'), and FORCE ('?').

    

Refresh Modes for Materialized Views –

Ø  ON DEMAND or ON COMMIT

Ø  In the case of   ON COMMIT, the materialized view is changed every time a transaction commits.

Ø In the case of   ON DEMAND materialized views, the refresh can be performed with refresh methods provided in either the DBMS_SYNC_REFRESH or the DBMS_MVIEW

Ø A materialized view that uses the ON STATEMENT refresh mode is automatically refreshed every time a DML operation is performed on any of the materialized view’s base tables.

o   To use the ON STATEMENT refresh mode, a materialized view must be fast refreshable. An index is automatically created on ROWID column of the fact table to improve fast refresh performance.

o   With the ON STATEMENT refresh mode, any changes to the base tables are immediately reflected in the materialized view. There is no need to commit the transaction or maintain materialized view logs on the base tables.

CREATE MATERIALIZED VIEW sales_mv_onstat

REFRESH FAST

ON STATEMENT

USING TRUSTED CONSTRAINT

AS

SELECT s.rowid sales_rid, c.cust_first_name first_name, c.cust_last_name last_name,

p.prod_name prod_name,

s.quantity_sold quantity_sold, s.amount_sold amount_sold

FROM sh.sales s, sh.customers c, sh.products p

WHERE s.cust_id = c.cust_id and s.prod_id = p.prod_id;

The DBMS_MVIEW package contains three APIs for performing refresh operations:

• DBMS_MVIEW.REFRESH: Refresh one or more materialized views.

DBMS_MVIEW.REFRESH('CAL_MONTH_SALES_MV', method => '?',atomic_refresh => FALSE, out_of_place => TRUE);

DBMS_MVIEW.REFRESH('CAL_MONTH_SALES_MV', 'F', '', TRUE, FALSE,0,0,0,

FALSE, FALSE);

    'CAL_MONTH_SALES_MV’: Name of the MV

                    ‘F’: Fast (The refresh method: F-Fast, P-Fast_PCT, ?-Force, C-Complete)

                     ‘ ’: The rollback segment to use.

                                                    TRUE: Refresh after errors (TRUE or FALSE)

                                   FALSE,0,0,0: used by the replication process

                                                  FALSE: Atomic refresh (TRUE or FALSE)

                                                  FALSE: Whether to use out-of-place refresh

v  Multiple materialized views can be refreshed at the same time, and they do not all have to use the same refresh method

DBMS_MVIEW.REFRESH('CAL_MONTH_SALES_MV, FWEEK_PSCAT_SALES_MV', 'CF', '',TRUE, FALSE, 0,0,0, FALSE, FALSE);

cal_month_sales_mv be completely refreshed and fweek_pscat_sales_mv be a fast refresh

o   DBMS_MVIEW.REFRESH_ALL_MVIEWS :  Refresh all materialized views.

Refreshing all materialized views is the following:

DBMS_MVIEW.REFRESH_ALL_MVIEWS(failures,'C','', TRUE, FALSE, FALSE);

o   DBMS_MVIEW.REFRESH_DEPENDENT: Refresh all materialized views that depend on a specified master table or materialized view or list of master tables or materialized views

DBMS_MVIEW.REFRESH_DEPENDENT(failures, 'CUSTOMERS', 'C', '', FALSE, FALSE, FALSE);

           

            Refresh after errors (TRUE or FALSE)

Atomic refresh (TRUE or FALSE)

Whether it is nested or not (TRUE or FALSE)

  Use the package DBMS_MVIEW.EXPLAIN_MVIEW to determine what refresh methods are available for a materialized view.

 You can use the DBMS_ADVISOR.TUNE_MVIEW procedure, which provides a script containing the statements required to create a fast refreshable materialized view.

  Materialized view logs must exist on all base tables of a materialized view that needs to be fast refreshed.

  exec DBMS_MVIEW.REFRESH('percentile_per_pdt', method => 'F');

  While a job is running, you can query the V$SESSION_LONGOPS view to tell you the progress of each materialized view being refreshed.

SELECT * FROM V$SESSION_LONGOPS;

  To look at the progress of which jobs are on which queue, use:

SELECT * FROM DBA_JOBS_RUNNING;

  Checking status of a materialized view: DBA_MVIEWS, ALL_MVIEWS, and USER_MVIEWS.

SELECT MVIEW_NAME, STALENESS, LAST_REFRESH_TYPE, COMPILE_STATE

FROM USER_MVIEWS ORDER BY MVIEW_NAME;

MVIEW_NAME        STALENESS     LAST_REF COMPILE_STATE

---------- --------- -------- --------------------------

CUST_MTH_SALES_MV NEEDS_COMPILE   FAST    NEEDS_COMPILE

PROD_YR_SALES_MV  FRESH           FAST     VALID

If the compile_state column shows NEEDS COMPILE, the other displayed column values cannot be trusted as reflecting the true status. To revalidate the materialized  view, issue the following statement:

ALTER MATERIALIZED VIEW [materialized_view_name] COMPILE;

  For fast refresh, create materialized view logs on all detail tables involved in a materialized view with the ROWID, SEQUENCE and INCLUDING NEW VALUES clauses.

  You can verify which partitions are fresh and stale with views such as DBA_MVIEWS and DBA_MVIEW_DETAIL_PARTITION.

Using Partitioning to Improve Data Warehouse Refresh

1. Place the new data into a separate table, sales_01_2001.
2. Gather statistics on the sales_01_2001 table.
3. Create indexes and add constraints on sales_01_2001
4. Indexes can be built in parallel and should use the NOLOGGING and COMPUTE STATISTICS options.

              CREATE BITMAP INDEX sales_01_2001_customer_id_bix

ON sales_01_2001(customer_id)

TABLESPACE sales_idx NOLOGGING PARALLEL 8 COMPUTE STATISTICS;

5.      Apply all constraints to the sales_01_2001 table that are present on the sales table.

ALTER TABLE sales_01_2001 ADD CONSTRAINT sales_customer_id

REFERENCES customer(customer_id) ENABLE NOVALIDATE;

6. Add the sales_01_2001 table to the sales table.

7. In order to add this new data to the sales table, you must do two things.

o   First,  you must add a new partition to the sales table.

ALTER TABLE sales ADD PARTITION sales_01_2001

VALUES LESS THAN (TO_DATE('01-FEB-2001', 'DD-MON-YYYY'));

o   Then, you can add our newly created table to this partition using the EXCHANGE PARTITION operation.

ALTER TABLE sales EXCHANGE PARTITION sales_01_2001

WITH TABLE sales_01_2001

INCLUDING INDEXES WITHOUT VALIDATION UPDATE GLOBAL INDEXES;

The EXCHANGE operation preserves the indexes and constraints that were already

present on the sales_01_2001 table. For unique constraints (such as the unique

constraint on sales_transaction_id), you can use the UPDATE GLOBAL INDEXES clause.

Removing data from a partitioned table

ALTER TABLE sales DROP PARTITION sales_01_1998;

ALTER TABLE sales EXCHANGE PARTITION sales_01_2001 WITH TABLE sales_01_2001

INCLUDING INDEXES WITHOUT VALIDATION UPDATE GLOBAL INDEXES;