Table of Contents
This chapter briefly introduces the DBMS_MVIEW package, and describes how to the use the procedures of the package.
DBMS_MVIEW provides information about materialized views, and procedures that use the REFRESH function to keep the views up-to-date.
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
DBMS_MVIEW.EXPLAIN_REWRITE
(
query IN VARCHAR2,
statement_id IN VARCHAR2
);
Parameter
| Parameter | Description |
|---|---|
| query | SQL query. |
| statement_id | 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')
SQL> select MV_OWNER, MV_NAME, QUERY, REWRITTEN_TXT, MESSAGE
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"
Refreshes a materialized view.
Details about the REFRESH procedure are as follows:
Prototype
DBMS_MVIEW.REFRESH
(
qualified_obj IN VARCHAR2,
refresh_method IN VARCHAR2
);
Parameter
| Parameter | Description |
|---|---|
| qualified_obj | Materialized view to refresh. |
| refresh_method | Refresh methods. Options are:
|
Example
call dbms_mview.refresh('myuser.mv_t','f');
call dbms_mview.refresh('mv_t','c');