Materialized Views

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;