Database Reference
In-Depth Information
CAPABILITY_NAME POSSIBLE MSGTXT
-------------------------- -------- ----------------------------------------
REWRITE_FULL_TEXT_MATCH Y
REWRITE_PARTIAL_TEXT_MATCH N grouping column omitted from SELECT list
REWRITE_GENERAL N grouping column omitted from SELECT list
Refreshes
When a table is modified, all dependent materialized views become stale. Therefore, for each stale materialized view,
a refresh is necessary. When you create a materialized view, you can specify how and when refreshes will take place.
To specify how the database engine performs refreshes, you can choose from these methods:
REFRESH COMPLETE
: The whole content of the container table is deleted, and all data is
reloaded from the base tables. Obviously, this method is always supported. You should use it
only when a sizable part of a base table has been modified or fast refresh isn't available due to
the complexity of the materialized view.
•
•
REFRESH FAST
: The content of the container table is reused, and only the modifications are
propagated to the container table. If little data has been modified in the base tables, this is the
method you should use. This method is available only if several requirements are fulfilled.
If one of them isn't fulfilled, either the
REFRESH FAST
is refused as a valid parameter of the
materialized view or an error is raised. Fast refreshes, as well as PCT refreshes (a special kind
of fast refreshes), are covered in detail in the next sections.
•
REFRESH FORCE
: At first, a fast refresh is attempted. If it doesn't work, a complete refresh is
performed. This is the default method.
•
NEVER REFRESH
: The materialized view is never refreshed. If a refresh is attempted,
it terminates with the
ORA-23538: cannot explicitly refresh a NEVER REFRESH
materialized view
error. You can use this method to make sure that a refresh will never be
performed.
You can choose the point in time when the refresh of a materialized view occurs in two different ways:
•
ON DEMAND
: The materialized view is refreshed when explicitly requested (either manually or
by running a job at a regular interval). This means that the materialized view may contain stale
data during the period of time from the modification of the base tables to the refresh of the
materialized view.
•
ON COMMIT
: The materialized view is automatically refreshed at the end of the transaction that
modifies the tables it's based on. In other words, the materialized view always contains fresh
data as far as the other sessions are concerned.
You can combine the options to specify how and when a materialized view is refreshed and use them with both
the
CREATE MATERIALIZED VIEW
and
ALTER MATERIALIZED VIEW
statements. Here's an example of this:
ALTER MATERIALIZED VIEW sales_mv REFRESH FORCE ON DEMAND
It's even possible to create a materialized view with the
REFRESH COMPLETE ON COMMIT
options. However, it's very
unlikely that such a configuration would be useful in practice.
