Chapter 20. DBMS_MVIEW

Table of Contents

20.1. Overview
20.2. Procedures
20.2.2. REFRESH

This chapter briefly introduces the DBMS_MVIEW package, and describes how to the use the procedures of the package.

20.1. Overview

DBMS_MVIEW provides information about materialized views, and procedures that use the REFRESH function to keep the views up-to-date.

20.2. Procedures

This section describes the procedures provided by the DBMS_MVIEW package, in alphabetical order.


Describes information about Sql query rewrite. Using this procedure, users can determine why an SQL query was not rewritten, which materialized view was used to rewrite the SQL query, or what the rewritten SQL query is.

The SQL query passed as a query parameter does not actually get executed, and its result is stored in the REWRITE_TABLE table of the current schema. Before calling this procedure, the $TB_HOME/scripts/rewrite_table.sql script must be executed to create the REWRITE_TABLE table.

Details about the EXPLAIN_REWRITE procedure are as follows:

  • Prototype

        query         IN   VARCHAR2,
        statement_id  IN   VARCHAR2
  • Parameter

    querySQL query.

    Unique identifier that the user specifies to distinguish between SQL query results.

    This identifier is stored in the STATEMENT_ID column of the REWRITE_TABLE table.

  • Example

    SQL> create table base as (select mod(level, 100) a, level*10 b from dual 
            connect by level<=100);
    SQL> create materialized view mv enable query rewrite as 
            select sum(a+b) s, count(b+a) c from base;
    SQL> @rewrite_table.sql
    SQL> exec dbms_mview.explain_rewrite('select avg(a+b) from base')
         from rewrite_table;
    MV_OWNER MV_NAME QUERY                  REWRITTEN_TXT            MESSAGE          
    -------- ------- --------------- ------------------------ ------------------------
    SYS      MV      SELECT AVG(A+B) SELECT ("MV"."S"         010: query was rewritten    
                     FROM BASE         / "MV"."C") "AVG(A+B)"   with materialized view  
                                     FROM "SYS"."MV"                              
                     select avg(a+b) SELECT ("MV"."S"         000: whole query was
                     from base         / "MV"."C") "AVG(A+B)"   written  
                                     FROM "SYS"."MV"                               

20.2.2. REFRESH

Refreshes a materialized view.

Details about the REFRESH procedure are as follows:

  • Prototype

        qualified_obj     IN   VARCHAR2,
        refresh_method    IN   VARCHAR2
  • Parameter

    qualified_objMaterialized view to refresh.

    Refresh methods.

    Options are:

    • C or c: Complete refresh.

    • F or f: Fast refresh.

    • ?: Default. Execute fast refresh if available. Otherwise, execute complete refresh.

  • Example

    call dbms_mview.refresh('myuser.mv_t','f');
    call dbms_mview.refresh('mv_t','c');