Database Reference
In-Depth Information
In case you want to automate a refresh on demand, with both
CREATE MATERIALIZED VIEW
and
ALTER
MATERIALIZED VIEW
, you can also specify the time of the first refresh (
START WITH
clause) and an expression that
evaluates to the time of subsequent ones (
NEXT
clause). For example, with the following SQL statement, a refresh is
scheduled every ten minutes starting from the time the SQL statement is executed:
ALTER MATERIALIZED VIEW sales_mv
REFRESH COMPLETE ON DEMAND
START WITH sysdate
NEXT sysdate+to_dsinterval('0 00:10:00')
To schedule the refreshes, a job based on the
dbms_job
package is automatically submitted. Notice that the
dbms_refresh
package is used instead of the
dbms_mview
package:
SQL> SELECT what, interval
2 FROM user_jobs;
WHAT INTERVAL
------------------------------------------- -----------------------------------
dbms_refresh.refresh('"CHRIS"."SALES_MV"'); sysdate+to_dsinterval('0 00:10:00')
reFreSh GrOUpS
The
dbms_refresh
package is used to manage
refresh groups
. a refresh group is simply a collection of one
or more materialized views. a refresh performed with the
refresh
procedure in the
dbms_refresh
package is
performed in a single transaction (
atomic_refresh
is set to
TRUE
). This behavior is necessary if the consistency
between several materialized views is to be guaranteed. This also means that either all materialized views
contained in the group are successfully refreshed or the whole refresh is rolled back.
Fast Refreshes with Materialized View Logs
During a fast refresh, the content of the container table is reused, and only the modifications are propagated from
the base tables to the container table. Obviously, the database engine is able to propagate the modifications only if
it knowns them. For that purpose, you have to create a
materialized view log
on each base table in order to enable
fast refreshes (partition change tracking fast refreshes, discussed in the next section, are an exception to this). For
example, in order to be fast-refreshed, the
sales_mv
materialized view requires materialized view logs on the
sales
,
customers
, and
products
tables.
Simply put, a materialized view log is a table that is automatically maintained by the database engine that tracks
the modifications that occur on a base table. In addition to materialized view logs, an internal log table is used for
direct-path inserts. You don't need to create it, because it's automatically installed when the database is created. To
display its content, you can query the
all_sumdelta
view.
In the simplest case, you create the materialized view logs with SQL statements like the following (this example is
based on the
mv_refresh_log.sql
script):
SQL> CREATE MATERIALIZED VIEW LOG ON sales WITH ROWID;
SQL> CREATE MATERIALIZED VIEW LOG ON customers WITH ROWID;
