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.


-- 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