Home  >  Oracle Database Administration (DBA)  >  Replication  >  Materialized views (=snapshot)

1. Definition

A materialized view is the images of a table or view at a specific time or the replication at that table in real time. Technically, the materialized view is created as a copy of the source table which could be updated periodically with the changes done on the source table. Sometimes the changes are not applied and the materialized view is recreated (if the MV doesn't need to be refreshed often and on the source table there are many DML is better to recreate the materialized view rather then to apply the latest DML). 

When a materialized view is created a new table is created. This table is refreshed regularly by a job created in the same time as the MV. However if the MV don't need to be refresh by a job, the job is not created (REFRESH ON DEMAND or REFRESH ON COMMIT are not creating a database job). 

2. Create the materialized view logs (MVL)

If the materialized view (MV) will be refreshed by applying the latest changes the MVL must be created. The MVL are tables situated on the source schema which keep the latest changes. When the MV is refreshed these logs are applied on the target MV.

In this example I will show how the MVs work in the same database (in general the replications are done between 2 or more databases; in this case the DBlinks are used to access the remote/ source tables/ MV logs). In my example the source table will be EMP from SCOTT user and the MV will be created on the PAUL account. 

The MVL are created on the source database/ schema:

CREATE MATERIALIZED VIEW LOG ON SCOTT.EMP;    (the PK is used)

2 new table are created: MLOG$_EMP   --> snapshot log for master table SCOTT.EMP

                                                  RUPD$_EMP   --> are created only if the base table has a PK. The rupd$_ table supports updateable

                                                               materialized views, which are only possible on log tables with primary keys.

CREATE MATERIALIZED VIEW LOG ON SCOTT.EMP with ROWID;    (the rowid is used)

3. Create the materialized view (MV)

          CREATE MATERIALIZED VIEW MV_EMP

          build immediate

          REFRESH FAST ON COMMIT

           AS SELECT * FROM scott.emp;

Don't forget to grant "ON COMMIT REFRESH" to paul:

        grant ON COMMIT REFRESH to paul;  (connected as sys)

A REFRESH ON COMMIT materialized view will be refreshed automatically when a transaction that does DML to one of the materialized view's detail tables commits. The time taken to complete the commit may be slightly longer than usual when this method is chosen. This is because the refresh operation is performed as part of the commit process.

           Build Methods

           Refresh Modes

           Refresh Options

    CREATE MATERIALIZED VIEW mv_emp2

    BUILD IMMEDIATE

    REFRESH FAST

    START WITH SYSDATE

    NEXT SYSDATE + 5/(24*60)

    AS SELECT * FROM scott.emp;

 

4. Finding the Oracle job associated with a specific materialized view

    SELECT * FROM USER_JOBS WHERE WHAT like '%<mv_name>%';

    SELECT * FROM USER_JOBS WHERE WHAT like '%MV_EMP2%';

5. Changing the refresh time for a particular materialized view 

begin

DBMS_REFRESH.CHANGE(

     name => 'PAUL.MV_EMP2',

     next_date => to_date('05-07-2007 23:00:00','DD-MM-YYYY HH24:MI:SS')

);

commit;

end;

/

This script will run the following script:

begin

SYS.DBMS_JOB.CHANGE (

      job => job_number,

      what => 'dbms_refresh.refresh(''"PAUL"."MV_EMP2"'');',

      next_date => to_date('20/08/2007 17:00:00','dd/mm/yyyy hh24:mi:ss'),

      interval => 'SYSDATE + 1'

);

commit;

end;

/

More information about  this subject ( Materialized views (=snapshot) ) you can get from  www.in-oracle.com

View is stored sql statement which would execute when ever you query on it, and it will query the base tables directly, On the other hand Materialized View, actually its a whole big topic, the key features are, it create a physical table with its own storage, its an advance form of snapshot, it could be refreshed when ever user query it, when ever the data gets commit in base tables or manually, if the user have QUERY REWRITE privilage and the materialized view has been made for getting aggrigate result in the same schema with the query rewrite option, and if the user’s select statement could get the same kind of result that you have in the materialized view then even though user is selecting base tables, but Oracle will get the result from the materialzed view.
 

For more information consult the following link.