In Oracle : Oracle Oracle Materialized views (=snapshot)

Oracle Applications EBS 11i, R12      Oracle Database      Data Warehouse & BI      Oracle Middleware      PL/SQL      Linux ...

Oracle DBA

Real Application Cluster (RAC) Maintenance tasks Backup and Recovery Database Architecture Replication Oracle Performance Tuning Oracle DBA - Other articles Oracle scripts/ Selects for DBA Oracle Errors (ORA-nnnnn)

The last articles in the site (HOT)

The most visited articles in the site

In-Oracle.com  -> Oracle DBA -> Oracle Replication

-> Oracle Materialized views (=snapshot)

 

 Oracle Materialized views (=snapshot)

 

 

1. Definition

2. Create the materialized view logs (MVL)

3. Create the materialized view (MV)

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

5. Changing the refresh time for a particular materialized view 

6. Putting the MV in the same refresh group

7. Finding the MV refresh group of a particular MV

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

Build Method Description
BUILD IMMEDIATE Create the materialized view and then populate it with data.
BUILD DEFERRED Create the materialized view definition but do not populate it with data.
           

           Refresh Modes

Refresh Mode Description
ON COMMIT Refresh occurs automatically when a transaction that modified one of the materialized view's detail tables commits. This can be specified as long as the materialized view is fast refreshable (in other words, not complex). The ON COMMIT privilege is necessary to use this mode
ON DEMAND Refresh occurs when a user manually executes one of the available refresh procedures contained in the DBMS_MVIEW package (REFRESH, REFRESH_ALL_MVIEWS, REFRESH_DEPENDENT)
 

           Refresh Options

Refresh Option Description
COMPLETE Refreshes by recalculating the materialized view's defining query.
FAST Applies incremental changes to refresh the materialized view using the information logged in the materialized view logs, or from a SQL*Loader direct-path or a partition maintenance operation.
FORCE Applies FAST refresh if possible; otherwise, it applies COMPLETE refresh.
NEVER Indicates that the materialized view will not be refreshed with refresh mechanisms.

           The following statement create a materialized view which is refreshed at every 15 minutes:

            

    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;

/

 

6. Putting the MV in the same refresh group

 

The MAKE procedure is used to create a new Refresh group and add in my case the MV_EMP & MV_EMP2 in this new MV refresh group:

begin

    DBMS_REFRESH.MAKE(

            name => 'paul_MV_group',

            list => ' mv_emp, mv_emp2',

            next_date => sysdate,

            interval => 'sysdate+1/(24*60)');

end;

 To remove the MV_EMP from the MV_EMP refresh group (created by default) you can run the following script:

begin

   DBMS_REFRESH.SUBTRACT(

            name => 'mv_emp',

            list => 'mv_emp');

end;

 

            7. Finding the MV refresh group of a particular MV

SELECT R.RNAME, S.OWNER, S.NAME

FROM DBA_SNAPSHOTS S,

           DBA_REFRESH R

WHERE S.REFRESH_GROUP = R.REFGROUP

     AND S.OWNER='PAUL'

     AND S.NAME='MV_EMP';

 

In-Oracle.com  -> Oracle DBA -> Oracle Replication

-> Oracle Materialized views (=snapshot)

Oracle Applications EBS 11i, R12      Oracle Database      Data Warehouse & BI      Oracle Middleware      PL/SQL      Linux ...

 

     Copyright (c) 2011-2015  www.in-oracle.com  |  Disclaimer: The views expressed on this web site are my own and do not reflect the views of Oracle Corporation. You may use the information from this site only at your risk.