Materialized Views overview
We are now
ready to examine the use of Oracle
materialized views (MVs). When accessing
data against any relational database, the
database developer is faced with a quandary
of aggregation. As we know, extracting
complex summaries and aggregations from a
database can clause repeated large-table
full-table scans against the database. For
very large systems, these kinds of large
queries can run for many hours.
So how can
we provide sub-second response time when the
queries may run for hours?
is with Oracle materialized views. An
Oracle materialized view allows us to
pre-summarize information and store inside
Oracle tables. With Oracle's query rewrite
facility enabled, Oracle will detect queries
that can use the materialized views and
automatically re-write the SQL to reference
the materialized view.
The query optimizer
can use materialized views by
when an existing materialized view can and
should be used to satisfy a request. It then
transparently rewrites the request to use
the materialized view. Queries are then
directed to the materialized view and not to
the underlying detail tables, which should
result in a significant performance gain.
revolutionary technique can be used to take
ordinary SQL queries down from hours to
sub-second response time. This illusion of
instantaneous response time is achieved by
pre-summarizing the data.
there is a downside to materialized views.
Because the materialized views are derived
from subsets of the Oracle data, the
information and the materialized views may
become stale as soon as additional data is
added into our database.
Hence, the Oracle
database provides a refresh mechanism for
materialized views. The Oracle professional
can specify that the materialized views be
refreshed instantly, every 10 minutes, every
day, and so on, depending on the volatility
of the data. Here is an example:
CREATE MATERIALIZED VIEW emp_sum
above example, the MV is re-created every
1/24 of a day (once per hour).
refresh interval gives the database
developer complete control over the refresh
interval for the materialized views, and
allows them to take long-running expensive
SQL queries and make then run super-fast.
Oracle8i, DBAs who have used summaries spent
a significant amount of time manually to
create them, identifying which ones to
create, index them, update them, and advise
their users on which ones to use.
To see how
MV’s work, here is an actual example of a
manual aggregation from a real data
Once the MV
is defined, Oracle will automatically check
the data dictionary to see of an MV can be
used to service the query.
Oracle will dynamically re-write your query
to reference the MV, and you will be able to
see this change when you gather the
execution plan for your SQL.
ENABLE QUERY REWRITE
AS SELECT deptno,job,SUM(sal)
GROUP BY deptno,job
Materialized View Created.