Materialized Views
Materialized views are disk based and update periodically base upon the query definition.
Views are virtual only
and run the query definition each time they are accessed.
Materialized views can
only be set to refresh automatically through the database detecting changes
when
the view query is
considered simple by the compiler. If it's considered too complex,
it won't be able to set
up what are essentially internal triggers to track changes in the source tables
to only update the
changed rows in the mview table.
When you create a
materialized view, you'll find that Oracle creates both the mview and as a
table
with the same name,
which can make things confusing.
Why Use Materialized
Views?
You can use materialized
views to achieve one or more of the following goals:
1.Ease Network Loads.
2.Create a Mass
Deployment Environment.
3.Enable Data
Subsetting.
4.Enable Disconnected
Computing.
EQ-
create MATERIALIZED
view user1
refresh complete
START WITH SYSDATE
NEXT SYSDATE + 1
as select
DISPLAY_NAME,OFFICE from wc_user_info_test1;
NOTE:-This MV is start from sysdate and next triggered fire for refresh is next day(sysdate+1).
-- First time refresh
that is a complete refresh
SQL> BEGIN
2
dbms_mview.REFRESH('mv_test');
3 END;
4 /
PL/SQL procedure
successfully completed.
SQL> SELECT
last_refresh_type, to_char(last_refresh_date, 'hh24:mi:ss')
2 FROM user_mviews
3 WHERE mview_name =
'MV_TEST';
LAST_REFRESH_TYPE
TO_CHAR(LAST_REFRESH_DAT
------------------------
------------------------
FAST_PCT 21:13:34
Read-Only Materialized Views
CREATE MATERIALIZED VIEW
hr.employees AS
SELECT * FROM
hr.employees@orc1.world;
Updatable Materialized Views
CREATE MATERIALIZED VIEW
hr.departments FOR UPDATE AS
SELECT * FROM
hr.departments@orc1.world;
