Materialized Views

SYNTEX:

CREATE MATERIALIZED VIEW VIEW_NAME

BUILD [IMMIDIATE | DEFFERED]

REFREASH [FAST | COMPLETE | FORCE]

ON [COMMIT | DEMAND]

WITH PRIMARY KEY [ROWID]

[[ENABLE | DISABLE] QUERY REWRITE]

AS

SELECT …..;

BUILD Clause

                -IMMEDIATE: The materialized view is populated immediately

                -DEFFRRED: The materialized view is populated on the first requested refresh

Refresh Types

                -Refresh fast

                -Refresh Complete

                -Refresh force

                - Never Refresh: MV will not refresh at any given time. It will create at the time MV create

Refresh Complete:  

It is the simplest way and expensive if there are more rows

It will recreate all the rows in MV by default. ROWID values will get changed

Oracle DB will re execute the MV query to refresh the MV

Refresh syntax:

Exec dbms_mview.refresh(‘mv’,’c’);

Here ‘mv’ is materialized view name and ‘c’ represents as refresh completely.

Refresh COMPLETE on COMIT:

ORA-12054: Cannot set the ON COMMIT refresh attribute for the MV.

To avoid this error ORA-12054, use MV WITH key word.

For example:

By default DEMAND MV refresh

CREATE MATERIALIZED VIEW MKVIEW

REFREASH COMPLETE ON COMMIT

WITH PRIMARY KEY /ROWID

As select * from EMP;

Any DML changes happed in base table, without refresh changes will be populated in the MV. That is the advantage of Refresh complete on commit command.

BUILD DEFERRED:

The value will not be populated in the MV but it will be populated on first requested refresh.

MV scheduled refresh:

 The MV will refresh based on the scheduled time. User/ developer will not use on COMMIT or refresh of the MV.

Syntax:

CREATE MATERIALIZED VIEW MKVIEW

REFREASH COMPLETE

START WITH (SYSDATE)

NEXT (SYSDATE +1/1440) ---24 HRS * 60 MINS ( Refresh will happen at this time)

As

Select * from EMP;

ROWID:

It’s unique ID, Oracle system itself will create and unique and 18 byte length

ENABLE QUERY REWRITE:

It will be used if we are performing any aggregate function in advance

For Example:

CREATE MATERIALIZED VIEW MKVIEW

REFREASH COMPLETE

ENABLE QUERY REWRITE

As

SELECT DEPT_ID, SUM (SAL) FROM EMP GROUP BY DEPT_ID HAVING SUM (SAL)>1000;

INCREMENTAL / REFRESH FAST:

MV will be fast refreshed only if there is any change in the base table. To maintain history of the base table, it requires a log table. It is known as MV log. It is named as MLOG$_<BASE_TABLE>.

If you specify refresh fast , then create MV will check for the MV log for each of the tables referenced by the MV, else it will fail to create.

When DML changes are made to master table data, Oracle database stores rows describing those changes in the MV log to refresh MVs based on the master table.

MV logs are located in master database in the same schema as master table. A master table can have only one MV log defined on it.

Refresh fast will perform refresh according to the changes occurred in the master table.

Syntax:

CREATE MATERIALIZED VIEW MKVIEW

REFREASH FAST

As

SELECT * FROM EMP;

It will through error because of there is no MV logs are present for the master table. So user has to create MV logs.

Syntax:

CREATE MATERIALIZED VIEW LOG ON EMP

WITH PRIMARY KEY or ROWID;

CREATE MATERIALIZED VIEW MKVIEW

REFREASH FAST

WITH ROWID ( By default it will refer PRIMARY KEY as we don’t have PRIMARY KEY ON base table so we have to use with clause with ROWID)

As

SELECT * FROM EMP;

Note: ROWID will not change REFRESH FAST FOR unchanged records

Benefits of Refresh Fast:

It will not create entire new result set using new ROWID like refresh complete

Values will be updated in MV without changing the ROWID

Once MV is fast refreshed then entries mentioned in the MV log will be removed

Refresh FORCE:

First it will try to do fast refresh. If MV log is either corrupted or not available then it will fail to refresh it fast and it will do refresh complete.

Syntax:

CREATE MATERIALIZED VIEW MKVIEW

REFREASH FORCE

WITH PRIMARY KEY

As

SELECT * FROM EMP;

EXEC DBMS_MVIEW.REFRESH (‘MKVIEW’,’?’); (here ‘?’ represents, we don’t know whether it will refresh either refresh fast or complete)

First time it will behave as refresh complete after then it will behave like refresh fast because of first time we don’t have log files.

Never Refresh:

In this Method, MV will never get refreshed. We include never refresh statement while crating MV. Later if you want to refresh/sync with the base table then you have to alter MV and then refresh it using DBMS_MVIEW.REFRESH.

Never Refresh will always use ‘REFRESH COMPLETE’ only.

Syntax:

CREATE MATERIALIZED VIEW MKVIEW

NEVER REFRESH

As

SELECT * FROM EMP;