Thread: materialized view

which process will refresh the materialized view ?

Originally Posted by patlollaraghav

which process will refresh the materialized view ?

The following two links may be helpful for you

http://www.akadia.com/services/ora_m...zed_views.html


http://www.dba-oracle.com/art_9i_mv.htm

The DBMS_MVIEW package is used to perform management actions such as evaluating,registering
,or refreshing a materialized view
To refresh a single materilized view,use DBMS_MVIEW.REFRESH.Its two main parameters are the
name of the materilized view and the method to refresh the mview.

complete refresh 'c'
fast refresh 'f'
force refresh '?'

example
execute dbms_mview.refresh('nameofmview','c');

To refresh multiple mviews
execute dbms_mview.refresh('mv1,mv2,mv3','cfc');

To refresh all mviews

execute dbms_mview.refresh_all;

The difference between the normal view and materialized view is, a normal view will have the statement only, whenever you try to open the view it will be compiled in that particular time and the result will be shown to you, but when you come to materialized view’s it will snap shot the master table (or tables) when you run the statement for the first time, and it will be stored as a schema object means like an entity type (not as an entity), that is why they called materialized views as snap shots when you want to refresh the materialized view follow the procedures which i had typed below. The attributes related to refreshing a materialized view are specified at the creation time of the materialized view (or later through the alter materialized view statement). The two most important attributes that impact the refresh time are: refresh fast or complete refresh on commit or on demand a fast refresh means an incremental refresh, which indicates that the materialized view will be refreshed based on only the data changes that have occurred in the master table(s). A complete refresh indicates that the materialized view will be refreshed by re-executing the query defining the materialized view. The on commit refresh indicates that the materialized view will be whenever the data in the master table changes. The on demand refresh indicates that the materialized view will be refreshed on demand by explicitly executing one of the refresh procedures in the dbms_mview package.

DBMS_MVIEW is the package which is used to refresh the materilized view.

e.g

Excecute DBMS_MVIEW.refresh(MVIEW_name, Type of refresh)




also,

DBMS_MVIEW.REFRESH - select one or more materialized views to refresh

DBMS_MVIEW.REFRESH_DEPENDENT - refresh those which depend on a table

DBMS_MVIEW.REFRESH_ALL_MVIEWS - refresh all materialized views

You can also specify to refresh the materialized view on commit on the base table .

Code:

create materialized view mv_bigtab
  build immediate
  refresh on commit
  enable query rewrite
as
select owner, count(*)
  from bigtab
 group by owner