which process will refresh the materialized view ?
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