MATERIALIZED VIEW

Contents of this Post

• Materialized View Concept
• Use Of Materialized View
• Create Materialized View
• Query Materialized View
• Materialized View Refresh duration
• Get Materialized View Data Definition
• Find All JOBs/MViews Related to Master DB
• Materialized View Error/Bug

Materialized View Concept

Oracle uses materialized views (also known as snapshots in prior releases) to replicate data to non-master sites in a replication environment and to cache expensive queries in a data warehouse environment. 

A materialized view is a replica of a target master from a single point in time. The master can be either a master table at a master site or a master materialized view at a materialized view site. Whereas in multi-master replication tables are continuously updated by other master sites, materialized views are updated from one or more masters through individual batch updates, known as a refreshes, from a single master site or master materialized view site.

Why not use a Table/View instead of a Materialized View?

One of the biggest benefit of using a materialized view is that Oracle takes care of keeping the data in sync. If you have a separate aggregate table, you are responsible for keeping the data synchronized. That generally requires a reasonable amount of code and a decent amount of testing and most organizations manage to make mistakes that leave holes that cause the aggregate table to get out of sync. This is particularly true when you try to implement incremental refreshes of the aggregate table.

Another major benefit is that, depending on the settings, Oracle can use query rewrite to use materialized views when users issue queries against base tables. So, for example, if you have a bunch of existing reports against a detail table that produce daily, monthly, and yearly aggregate results, you can create a materialized view on the base table that aggregates the data at a daily level and the optimizer can utilize that materialized view for all your existing queries. This makes it much easier to optimize reporting workloads in a data warehouse without trying to go and rewrite dozens of reports to use your new aggregate table or to mess with DBMS_ADVANCED_REWRITE to force your own rewrites of the queries.

Materialized views can be set to refresh manually, on a set schedule, or based on the database detecting a change in data from one of the underlying tables. Materialized views can be incrementally updated (delta changes) by combining them with materialized view logs, which act as change data capture sources on the underlying tables.

Materialized views are most often used in data warehousing / business intelligence applications where querying large fact tables with thousands of millions of rows would result in query response times that resulted in an unusable application.

Materialized views are primarily used to increase the application performance when it isn't feasible or desirable to use a standard view with indexes applied to it. Index can be created on Materialized views whereas the indexing is not possible in force view as the views are essentially logical table-like structures populated on the fly by a given query and the results of a view query are not stored anywhere on disk/datafile/segment.

Create Materialized View

Master Database Site

SELECT * FROM DBA_MVIEW_LOGS WHERE MASTER='<BASE TABLE NAME>',

CREATE MATERIALIZED VIEW LOG ON <BASE TABLE> TABLESPACE <TABLESPACE NAME> WITH <PRIMARY KEY>/<ROWID>;

DROP MATERIALIZED VIEW LOG ON <Schema>.<Table_Name>;

MView Database Site

CREATE DATABASE LINK <NAME> CONNECT TO <Master DB Schema Name> 

IDENTIFIED BY < Master DB Schema Password> USING '<Master DB TNS>';

CREATE MATERIALIZED VIEW "USERNAME"."MVIEW_NAME"

ORGANIZATION HEAP PCTFREE 10 PCTUSED 0 INITRANS 2 MAXTRANS 255 NOCOMPRESS NOLOGGING

STORAGE(INITIAL 1048576 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645

PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)

TABLESPACE "<Tablespace Name>"

BUILD IMMEDIATE

--BUILD DEFERRED                                                      -- Oracle Database does not execute the defining query immediately

USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255

STORAGE(INITIAL 1048576 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645

PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)

TABLESPACE ""<Tablespace Name>"

REFRESH FAST ON DEMAND START WITH sysdate+0 NEXT TRUNC(SYSDATE+1)+6/24 -- Morning 6 AM

-- REFRESH FAST ON DEMAND START WITH sysdate+0 NEXT SYSDATE + 5/(60*24)  -- Every 5 Mins

-- REFRESH COMPLETE ON DEMAND START WITH sysdate+0 NEXT SYSDATE + 30/(60*24) -- Every 30 Mins

-- REFRESH COMPLETE ON DEMAND START WITH sysdate+0 NEXT SYSDATE + 1

WITH PRIMARY KEY USING DEFAULT LOCAL ROLLBACK SEGMENT

DISABLE QUERY REWRITE

--WITH ROWID USING DEFAULT LOCAL ROLLBACK SEGMENT

DISABLE QUERY REWRITE

AS SELECT

COLUMN_1 Col_Alias_Name ,

COLUMN_2 Col_Alias_Name,

COLUMN_3 Col_Alias_Name,

--NULL Col_Alias_Name,,

--CAST(NULL as varchar2(1)) Col_Alias_Name,,

FROM <Master Schema>.<Master Base Table>@<DB_LINK Name>;

Refresh Complex MViews Using Trigger

create or replace trigger X

after insert or update or delete on T

declare

   l_job number;

begin

   dbms_job.submit( l_job, 'dbms_mview.refresh( ''OPS$TKYTE.T_MV'' );' );

end;

ALTER/DROP MATERIALIZED VIEW

ALTER MATERIALIZED VIEW <MVIEW_NAME>

REFRESH COMPLETE ON DEMAND START WITH sysdate+0 NEXT SYSDATE + 30/(60*24);

DROP MATERIALIZED VIEW LOG ON <MVIEW_NAME>;

Query Materialized View

Find All JOBs/MViews Related to Master DB

col JOB FOR 9999

col OWNER for a8

col MASTER_LINK for a15

col HOST for a10

col FAST_REFRESHABLE for a8

col STALENESS for a9

col COMPILE_STATE for a8

col MVIEW_NAME for a20

col "REFRH MOD-METHOD BLD_MOD" for a22

col INTERVAL FOR a24

SELECT 

         JOB

,a.OWNER,MVIEW_NAME

--,CONTAINER_NAME

,UPDATABLE

,REWRITE_ENABLED

,MASTER_LINK

,c.HOST

,REFRESH_MODE||'-'||REFRESH_METHOD||'-'||BUILD_MODE "REFRH MOD-METHOD BLD_MOD"

,FAST_REFRESHABLE

,LAST_REFRESH_TYPE

,to_char(LAST_REFRESH_DATE,'dd-Mon-yy hh24:mi:ss') LAST_REFRESH_DATE

,COMPILE_STATE

,INTERVAL

--,STALENESS

--,STALE_SINCE 

,USE_NO_INDEX

FROM DBA_MVIEWS a, DBA_REFRESH_CHILDREN b, DBA_DB_LINKS c

WHERE a.MVIEW_NAME=b.NAME

AND a.OWNER=b.OWNER

And MASTER_LINK in (SELECT '@'||DB_LINK from dba_db_links where UPPER(HOST) ='<Master DB Name>')

AND SUBSTR(a.MASTER_LINK,2) = c.DB_LINK

AND UPPER(c.HOST) ='<Master DB Name>'                                              -- PUT DB Name Here

Order by MASTER_LINK, MVIEW_NAME;

OR For Multiple DBs

SELECT

         JOB

,a.OWNER,MVIEW_NAME

--,CONTAINER_NAME,UPDATABLE,REWRITE_ENABLED

,MASTER_LINK

,REFRESH_MODE||'-'||REFRESH_METHOD||'-'||BUILD_MODE "REFRH MOD-METHOD BLD_MOD"

,FAST_REFRESHABLE

,LAST_REFRESH_TYPE

,to_char(LAST_REFRESH_DATE,'dd-Mon-yy hh24:mi:ss') LAST_REFRESH_DATE

,COMPILE_STATE

,INTERVAL

--,STALENESS,USE_NO_INDEX,STALE_SINCE  

from dba_mviews a, dba_refresh_children b

where a.MVIEW_NAME=b.NAME

And a.OWNER=b.OWNER

And MASTER_LINK in (SELECT '@'||DB_LINK from dba_db_links where UPPER(HOST) in (<Master DBs Name>))

Order by MASTER_LINK, MVIEW_NAME;

DBMS_JOBs + Materialized View

SELECT OWNER,NAME,JOB,INTERVAL from dba_refresh_children order by NAME;

Query All COLUMNS Of Materialized View

SELECT * FROM DBA_MVIEW_LOG_FILTER_COLS;

Get Materialized View Data Definition

SET LONG 2000000

SELECT DBMS_METADATA.GET_DDL( 'MATERIALIZED_VIEW','<MView Name>','<Schama Name>') FROM DUAL;

Query Materialized View Log in Master DB Site

SELECT * FROM DBA_MVIEW_LOGS WHERE MASTER='<BASE TABLE NAME>';

Materialized View Refresh duration

select 

         owner,mview_name, last_refresh_date as "Refresh Start"

,round(fullrefreshtim/60) as "Full Minutes to Refresh"

,round(increfreshtim/60) as "Inc Minutes to Refresh"

from ALL_MVIEW_ANALYSIS

where mview_name = '<MView Name>';

FULLREFRESHTIM >> Last Apx time that it took to fully refresh the materialized view

INCREFRESHTIM >> Last Apx time that it took to incrementally refresh the materialized view

Below query will provide more details of MView refresh duration.

set pages 600

col OWNER for a15
col MVIEW_NAME for a30
col "Current Refresh Schedule" trunc head "Current Refresh|Schedule"

col "Last Complete Refresh In Sec" trunc head "Last Complete|Refresh In Sec"

col "Last Increment Refresh In Sec" trunc head "Last Increment|Refresh In Sec"

col "Total Refresh Time In Hours" trunc head "Total Refresh|Time In Hours"

SELECT

DISTINCT Job_Id

,OWNER

,mview_name

,REFRESH_METHOD "Current Refresh Schedule"

,TO_CHAR(START_TIME,'DD-Mon-YY HH24:MI:SS')||' TO '||TO_CHAR(END_TIME,'DD-Mon-YY HH24:MI:SS') "Last Refresh Start-End Time"

,fullrefreshtim "Last Complete Refresh In Sec"

,increfreshtim "Last Increment Refresh In Sec"

,round(TOTAL_TIME/60/60,2) "Total Refresh Time In Hours"

FROM (

SELECT

a.job Job_Id,a.TOTAL_TIME

    ,c.OWNER,c.mview_name,c.last_refresh_date "START_TIME",c.REFRESH_METHOD

    ,CASE

      WHEN c.fullrefreshtim <> 0 THEN

          c.LAST_REFRESH_DATE + c.fullrefreshtim/60/60/24

      WHEN c.increfreshtim <> 0 THEN

          c.LAST_REFRESH_DATE + c.increfreshtim/60/60/24

      ELSE

          c.LAST_REFRESH_DATE

    END "END_TIME"

    ,c.fullrefreshtim

    ,c.increfreshtim

FROM 

         dba_jobs a

,dba_refresh_children b

,dba_mview_analysis c

WHERE a.job=b.job

AND   a.SCHEMA_USER=b.OWNER

AND   b.OWNER=c.OWNER

AND   b.OWNER=c.MVIEW_TABLE_OWNER

AND   b.NAME=c.mview_name)

ORDER BY OWNER,mview_name;

Other Materialized View Related Query

col INTERVAL for a35

select * from dba_refresh_children;

select * from dba_refresh;

SELECT * From dba_rgroup;

DBA_BASE_TABLE_MVIEWS

DBA_MVIEWS

DBA_MVIEW_REFRESH_TIMES

DBA_MVIEW_AGGREGATES

DBA_MVIEW_ANALYSIS

DBA_MVIEW_LOGS

DBA_MVIEW_DETAIL_RELATIONS

DBA_MVIEW_JOINS

DBA_MVIEW_KEYS

DBA_MVIEW_LOG_FILTER_COLS

DBA_REGISTERED_MVIEWS

DBA_REGISTERED_MVIEW_GROUPS

Materialized View Errors/Bug

Bug 1

Assumption: Source is 11.2 (11gR2) DB and Destination is 9.2 (9i) DB

CREATE MATERIALIZED VIEW "SCHEMA"."TABBLE"

*

ERROR at line 1:

ORA-04052: error occurred when looking up remote object T.C@DB_LINK

ORA-00604: error occurred at recursive SQL level 1

ORA-03120: two-task conversion routine: integer overflow

ORA-02063: preceding line from DB_LINK

Cause :-

Internet sources indicate that ORA-03120 is associated with bug 5671074 on Oracle Enterprise Edition; Version 9.2.0.8, occurring on any platform basically while the source DB is 11gR2

Action :-

It will be necessary to apply the one-off patch for Bug 5671074 to the 9.2.0.8 database. 

One-off patches to 9.2.0.8 for most platforms are already available for download on MOSC. 

Bug 5671074 to the 9.2.0.8 database. One-off patches to 9.2.0.8 for most platforms are already available for download on MOSC. 

Bug 5671074 : MATERIALIZED VIEW REFRESH FAILS WITH ORA-04052 ERROR 

https://support.oracle.com/epmos/faces/BugDisplay?id=5671074&_adf.ctrl-state=vb5ewdw7y_40&_afrLoop=394925371245165

Bug 2

Assumption: Source is 11.2 (11gR2) DB and Destination is 9.2 (9i) DB
 

CREATE MATERIALIZED VIEW TEST.TABLE_1

* 

ERROR at line 1: 

ORA-03113: end-of-file on communication channel 

Alert Log Says

Errors in file /u01/app/oracle/92/rdbms/log/DBUAT92_ora_3428.trc: 

ORA-07445: exception encountered: core dump [kghfrf()+32] [SIGSEGV] [Address not mapped to object] [0xFFFFFFFFFFFFFFE8] [] []  

Cause :-

The Oracle 9.2 database encounters a known bug.

ORA-07445 [kghfrf] Error When Trying to Create a Materialized View (Doc ID 1208712.1)

Action :-

Apply Patch 5879082 to the 9.2 database that should hold the Materialized View definition.

https://support.oracle.com/epmos/faces/DocumentDisplay?_afrLoop=20352788489886&id=1208712.1&_afrWindowMode=0&_adf.ctrl-state=kemj13thf_142