Materialized views

Oracle Materialized views

Materialized view : It is a physical table selective portion of the data from table’s periodically can be seen, it is used to improve the performance, useful in distributed environment.

Refresh : We can refresh the Materialized views frequently to get the new data into it.

Fast Refresh : This doesn't work for complex query. This is only applicable for a simple query like one-table access.

Complete Refresh : This requires the same amount of time like recreating the materialized view contents.

Refresh Force : Refreshes the materialized view as fast,if there are tables which can satisfy the conditions.

Privileges to Create Materialized Views

grant query rewrite to scott;
grant create materialized view to scott;
alter session set query_rewrite_enabled = true;

Just needed to grant the CREATE MATERIALIZED VIEW system privilege.
Challa> select * from user_sys_privs;

USERNAME PRIVILEGE ADM
________________________________________
Challa CREATE MATERIALIZED VIEW NO

Roles granted are listed below for completeness of the topic.
Challa> select * from user_role_privs;

USERNAME GRANTED_ROLE ADM DEF OS_
________________________________________
Challa CONNECT NO YES NO
Challa RESOURCE NO YES NO

We are running Oracle10g Release 2 Standard Edition in which QUERY REWRITE is not an enabled feature. Describing what I mean, I create a test table below upon which the materialized view will be based.

Challa> create table t1 as select owner,object_name,object_type from all_objects;
Table created.

Challa> select count(*) from t1;

COUNT(*)
———-
2997

Again when I attempt to create the materialized view with the “enable query rewrite” clause, I encounter the “feature not enabled” error.

Challa> create materialized view mv_test
2 refresh complete on commit
3 enable query rewrite
4 as
5 select object_type, count(*)
6 from t1
7 group by object_type;

from t1
*
ERROR at line 6:
ORA-00439: feature not enabled: Materialized view rewrite
I remove the “enable query rewrite” clause and the materialized view is created successfully.

Challa> create materialized view mv_test
2 refresh complete on commit
3 as
4 select object_type, count(*)
5 from t1
6 group by object_type;

Materialized view created.

Packages to Refresh The Materialized Views :

The DBMS_MVIEW package contains three APIs for performing refresh operations:

• DBMS_MVIEW.REFRESH
  Refresh one or more materialized views.
• DBMS_MVIEW.REFRESH_ALL_MVIEWS
  Refresh all materialized views.
• DBMS_MVIEW.REFRESH_DEPENDENT
  Refresh all materialized views that depend on a specified master table or materialized view or list of master tables or materialized views.

Monitoring a Refresh :

While a job is running, you can query the V$SESSION_LONGOPS view to tell you the progress of each materialized view being refreshed.

SELECT * FROM V$SESSION_LONGOPS;

To look at the progress of which jobs are on which queue, use:

SELECT * FROM DBA_JOBS_RUNNING;

Packages to Refresh The Materialized Views :


The DBMS_MVIEW package contains three APIs for performing refresh operations:
DBMS_MVIEW.REFRESH
Refresh one or more materialized views.
DBMS_MVIEW.REFRESH_ALL_MVIEWS
Refresh all materialized views.
DBMS_MVIEW.REFRESH_DEPENDENT
Refresh all materialized views that depend on a specified master table or materialized view or list of master tables or materialized views.


Monitoring a Refresh :

While a job is running, you can query the V$SESSION_LONGOPS view to tell you the progress of each materialized view being refreshed.


          SELECT * FROM V$SESSION_LONGOPS; 

To look at the progress of which jobs are on which queue, use:


          SELECT * FROM DBA_JOBS_RUNNING;

Checking the Status of a Materialized View :

Three views are provided for checking the status of a materialized view:

DBA_MVIEWS, ALL_MVIEWS, and USER_MVIEWS.

To check if a materialized view is fresh or stale, issue the following 

statement:

SELECT MVIEW_NAME, STALENESS, LAST_REFRESH_TYPE, COMPILE_STATE 
FROM USER_MVIEWS ORDER BY MVIEW_NAME;

MVIEW_NAME                          STALENESS                 LAST_REF              COMPILE_STATE
----------                                             ---------                       --------                            -------------
CUST_MTH_SALES_MV     NEEDS_COMPILE           FAST                      NEEDS_COMPILE
PROD_YR_SALES_MV          FRESH                              FAST                              VALID

If the compile_state column shows NEEDS COMPILE, the other displayed column values cannot be trusted as reflecting the true status. To revalidate the materialized view, issue the following statement:

ALTER MATERIALIZED VIEW [materialized_view_name] COMPILE;

Then reissue the SELECT statement.

Scheduling Refresh
Very often you have multiple materialized views in the database. Some of these can be computed by rewriting against others. This is very common in data warehousing environment where you may have nested materialized views or materialized views at different levels of some hierarchy.

In such cases, you should create the materialized views as BUILD DEFERRED, and then issue one of the refresh procedures in DBMS_MVIEW package to refresh all the materialized views. Oracle Database computes the dependencies and refreshes the materialized views in the right order. Consider the example of a complete hierarchical cube described in "Examples of Hierarchical Cube Materialized Views". Suppose all the materialized views have been created as BUILD DEFERRED. Creating the materialized views as BUILD DEFERRED only creates the metadata for all the materialized views. And, then, you can just call one of the refresh procedures in DBMS_MVIEW package to refresh all the materialized views in the right order:

DECLARE numerrs PLS_INTEGER;
BEGIN DBMS_MVIEW.REFRESH_DEPENDENT (
   number_of_failures => numerrs, list=>'SALES', method => 'C');
DBMS_OUTPUT.PUT_LINE('There were ' || numerrs || ' errors during refresh');
END;
/

Challa.