Scribd
Upload
48 views

Materialized Views: Snapshots

A materialized view stores the results of a query in the database to improve query performance. It benefits queries by precalculating joins and aggregations. A materialized view must be refreshed when the underlying tables change. It can be refreshed on demand or on commit. Materialized view logs capture changes to the base table to allow fast refreshes of just the changes rather than a full refresh. Monitoring views provide information on materialized views.

Uploaded by

Hazarath
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
48 views

Materialized Views: Snapshots

A materialized view stores the results of a query in the database to improve query performance. It benefits queries by precalculating joins and aggregations. A materialized view must be refreshed when the underlying tables change. It can be refreshed on demand or on commit. Materialized view logs capture changes to the base table to allow fast refreshes of just the changes rather than a full refresh. Monitoring views provide information on materialized views.

Uploaded by

Hazarath
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
You are on page 1/ 3

Materialized Views

A materialized view is a database object that stores the results of a query (possibly from a remote
database). Materialized views are sometimes referred to as snapshots

Benefits of materilazed views

1) the purpose of the materialized view is to increase query execution performance


2) a materialized view consumes storage space and must be updated when the underlying detail tables are
modified
3) DBA can create or drop materialized views at any time without affecting the SQL applications
4)

When creating a materialized view, you have the option of specifying whether the refresh occurs ON
DEMAND or ON COMMIT. In case of an ON COMMIT scenario, when a transaction commits, the
materialized view is refreshed and as a result, the data is always current in a materialized view. In case
of an ON DEMAND type, calling the dbms_mview package procedures refreshes the materialized view

Example

CREATE MATERIALIZED VIEW items_summary_mv


ON PREBUILT TABLE
REFRESH FORCE AS
SELECT a.PRD_ID, a.SITE_ID, a.TYPE_CODE, a.CATEG_ID,
sum(a.GMS) GMS,
sum(a.NET_REV) NET_REV,
sum(a.BOLD_FEE) BOLD_FEE,
sum(a.BIN_PRICE) BIN_PRICE,
sum(a.GLRY_FEE) GLRY_FEE,
sum(a.QTY_SOLD) QTY_SOLD,
count(a.ITEM_ID) UNITS
FROM items@remotedb a
GROUP BY a.PRD_ID, a.SITE_ID, a.TYPE_CODE, a.CATEG_ID;

Force a refresh:

Create a refresh group, defined to refresh every minute and assign our materialized view to it.

BEGIN
DBMS_REFRESH.make(
name => 'SCOTT.MINUTE_REFRESH',
list => '',
next_date => SYSDATE,
interval => '/*1:Mins*/ SYSDATE + 1/(60*24)',
implicit_destroy => FALSE,
lax => FALSE,
job => 0,
rollback_seg => NULL,
push_deferred_rpc => TRUE,
refresh_after_errors => TRUE,
purge_option => NULL,
parallelism => NULL,
heap_size => NULL);
END;
/

BEGIN
DBMS_REFRESH.add(
name => 'SCOTT.MINUTE_REFRESH',
list => 'SCOTT.EMP_MV',
lax => TRUE);
END;
/

exec DBMS_REFRESH.REFRESH('my_grp');

Create Materialized View Logs

Since a complete refresh involves truncating the materialized view segment and re-populating it using the
related query, it can be quite time consuming and involve a considerable amount of network traffic when
performed against a remote table. To reduce the replication costs, materialized view logs can be created
to capture all changes to the base table since the last refresh. This information allows a fast refresh,
which only needs to apply the changes rather than a complete refresh of the materialized view.

To take advantage of the of the fast refresh, connect to the master instance and create the materialized
view log.

CONNECT scott/tiger@db1

CREATE MATERIALIZED VIEW LOG ON scott.emp


TABLESPACE users
WITH PRIMARY KEY
INCLUDING NEW VALUES;

Monitoring

Here are some views that can be queried to obtain more information about materialized views:

select * from all_refresh;


select * from all_refresh_children;
select * from sys.v_$mvrefresh;
The Need for Materialized Views

You can use materialized views in data warehouses to increase the speed of queries on very large
databases. Queries to large databases often involve joins between tables, aggregations such as
SUM, or both. These operations are expensive in terms of time and processing power. The type of
materialized view you create determines how the materialized view is refreshed and used by
query rewrite.

You can use materialized views in a number of ways, and you can use almost identical syntax to
perform a number of roles. For example, a materialized view can replicate data, a process
formerly achieved by using the CREATE SNAPSHOT statement. Now CREATE MATERIALIZED VIEW
is a synonym for CREATE SNAPSHOT.

Materialized views improve query performance by precalculating expensive join and aggregation
operations on the database prior to execution and storing the results in the database. The query
optimizer automatically recognizes 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 go directly to the materialized view and not to the underlying detail tables. In general,
rewriting queries to use materialized views rather than detail tables improves response. Figure 8-
1 illustrates how query rewrite works.

You might also like