Databases Reference
In-Depth Information
There's more...
Now that we have seen the potential of materialized views, let's take an in depth look at this
useful database object. We will also try to answer the frequently asked question "Can I use
materialized views in an OLTP environment?"
Materialized views in depth
We have seen how to write a statement for creating materialized views; there are many
options that can be used while creating a materialized view, such as the following:
F
The
BUILD
IMMEDIATE
clause builds the materialized view immediately when
the command is executed. Instead if you use
BUILD
DEFERRED
, the data in the
materialized view is populated in the next refresh operation.
F
The
REFRESH
ON
COMMIT
clause forces the database to refresh data in the
materialized view, when a change is committed to one of the base tables. This
type of a refresh is
FAST
(incremental).
F
We can also use the
REFRESH
ON
DEMAND
clause to instruct the database to
refresh the materialized view. Using this command, data is refreshed only when
we want—using the
DBMS_MVIEW
refresh procedures.
F
Using the
START
WITH
and
NEXT
clauses, we can specify when to start the automatic
refresh operations and the interval between consecutive refreshes, respectively.
F
We can query the
DBA_MVIEWS
dynamic performance view, to obtain details
about all the materialized views available in the database, or the corresponding
USER_MVIEWS
and
ALL_MVIEWS
to restrict the results to the materialized views
the current user owns or can access. These views contain the materialized view
statement and the implementation details.
Materialized views and grants
To create a materialized view there are a few required privileges:
F
CREATE
SESSION
F
CREATE
TABLE
F
CREATE
MATERIALIZED
VIEW
F
QUERY
REWRITE
QUERY
REWRITE
has to be granted directly to the user who will use the materialized view,
not granted to a user through a role.
