Materialized view-Beginners Guide

on
One of the reasons why I started this blog was to write about the materialized views. Something you learn in the early stage of your career stays with you for the rest of your life. When I was having 3 months of experience as an Oracle DBA, we were getting mail from the customer that the materialized view is taking a long time to refresh. Could you please help us on this?

Seniors were checking on it. By the time they were checking the issue. I read about queries to find the explain plan of the SQL query, after reading for some time. I went to the senior and told them we can find the explain plan using the query. We found that the query took a lot of time in the db_link and mview refresh type was complete refresh. So we have suggested the action plan make it a fast refresh mview to keep less load on the db_link.

My Senior asked me re-write the mview to make it fast refreshable. I have to spend around 2 days to read of the about fast refreshable mview. Finally, the conclusion was to create two fast refreshable to mivew then create a view which joins this mview as inline queries are not supported by the miew.
This is my very first performance tuning. So every time I come across the word mview, this whole scene just flashes.

A Materialized view is a table whose rows are updated periodically based on the refresh schedule and type. In the case of a normal view, the SQL query will be saved as a view, and every time we run the view, the whole query gets executed and the result will be fetched.
Whereas in the case of mview, the mview query output will be saved in the table(same as mview name) and table values will be populated as per refresh type and schedule, so every time we query the mivew results from the mivew table will be fetched to the user.

The basic Syntax of Materialized view is as follows: 

CREATE MATERIALIZED VIEW mview-name
BUILD [IMMEDIATE | DEFERRED]
REFRESH [FAST | COMPLETE | FORCE ]
ON [COMMIT | DEMAND ]
[[ENABLE | DISABLE] QUERY REWRITE]
AS
SELECT column1, column2, column3, ...
FROM source_table1,source_table2,...
WHERE condition;

A refresh can be triggered in one of two ways.

ON COMMIT: The refresh is triggered when the data change of the dependent tables is committed. It is better to avoid this as the commit might take longer than usual as it has to wait for the mview refresh to complete.
ON DEMAND: The refresh can be initiated manually or a scheduled task.

There are three refresh types available for materialized views in the Oracle Database.
COMPLETE
FAST 
FORCE 

COMPLETE:
For every complete mivew refresh, it deletes all the rows in the mivew table and repopulates the mview table from the associated query. Thus generating a lot of archive logs during the refresh process. Please find the sample query below to better understand the complete refresh.
Delete from mview_tablename;

INSERT INTO mview_tablename (column1, column2, column3, ...)
SELECT column1, column2, column3, ...
FROM source_table1,source_table2,...
WHERE condition;
Reference post(Find which program/query is generating more archive log in oracle database).
The good thing about complete refreshable mivew is that is simple and easy to create as it doesn't have many restrictions like fast refreshable mivew.

FAST:

In order for the materialized view to be fast refreshable, all the tables should have their own mlog to record each change that occurs in the table. Am putting some restrictions on fast refreshable mview. Please check this link for complete details of the restrictions.
  • Mview cannot contain a SELECT list subquery.
  • MVIEW cannot contain a HAVING clause with a subquery.
  • Mview cannot contain nested queries that have ANY, ALL, or NOT EXISTS.

and the list goes on. The fast refreshable materialized view is like applying archive logs after the database restore. When we create the mview for the first time it looks for whether the mlog is present for the tables used in the mview create a statement if not the build fails with an error. Once we create mlog for all the tables the build should be successful if it meets the conditions of the fast refreshable mview.
Once the fast refreshable mview is created, mlog should be responsible to record the changes at the table level. So when the next mview refresh happens only the changes recorded in the mlog will be applied to the mivew table.


FORCE:
It will try to perform the fast refresh but if not possible then the complete refresh would be performed.

Syntax to refresh the mview: 
EXEC DBMS_MVIEW.refresh('MVIEW_NAME');

Comments

Post a Comment