Materialized view

A materialized view is a database object that contains the results of a query. They are local copies of data located remotely, or are used to create summary tables based on aggregations of a table's data.Materialized view and the Query rewrite feature is added from ORACLE 8i.

A materialized view log is a schema object that records changes to a master table's data so that a materialized view defined on the master table can be refreshed incrementally.

• If you delete any record from your Materialized view it is goanna impact your Source table once it is refreshed.

Examples to the Simple Materialized view’s is given below .

Eg 1 :Create materialized_view MV refresh as select * from emp;

Execute dbms_mview.refresh(‘MV’);

Refresh Complete : To perform a complete refresh of a materialized view, the server that manages the materialized view executes the materialized view's defining query, which essentially recreates the materialized view. To refresh the materialized view, the result set of the query replaces the existing materialized view data. Oracle can perform a complete refresh for any materialized view. Depending on the amount of data that satisfies the defining query, a complete refresh can take a substantially longer amount of time to perform than a fast refresh.

Create Materialized_view MV Refresh complete as select * from emp;

execute DBMS_mview.refresh(List=>’MV’,Method=>’c’);

Refresh Fast :To perform a fast refresh, the master that manages the materialized view first identifies the changes that occurred in the master since the most recent refresh of the materialized view and then applies these changes to the materialized view. Fast refreshes are more efficient than complete refreshes when there are few changes to the master because the participating server and network replicate a smaller amount of data.

Create Materialized_view MV Refresh fast as select * from emp;

execute DBMS_mview.refresh(list=>’MV’,Method=>’F’);

Primary Key Materialized Views :

The following statement creates the primary-key materialized view on the table emp located on a remote database.

SQL> 	CREATE MATERIALIZED VIEW mv_emp_pk
   	REFRESH FAST START WITH SYSDATE 
	NEXT  SYSDATE + 1/48
	WITH PRIMARY KEY 
	AS SELECT * FROM emp@remote_db;

Note: When you create a materialized view using the FAST option you will need to create a view log on the master tables(s) as shown below:

SQL> CREATE MATERIALIZED VIEW LOG ON emp;Materialized view log created.

Rowid Materialized Views :

The following statement creates the rowid materialized view on table emp located on a remote database:

SQL>CREATE MATERIALIZED VIEW mv_emp_rowid REFRESH WITH ROWID    AS SELECT * FROM emp@remote_db;

Materialized view log created.

Creating Materialized Aggregate Views :

CREATE MATERIALIZED VIEW sales_mv BUILD IMMEDIATE REFRESH FAST ON COMMIT AS SELECT t.calendar_year, p.prod_id, SUM(s.amount_sold) AS sum_sales FROM times t, products p, sales s WHERE t.time_id = s.time_id AND p.prod_id = s.prod_id GROUP BY t.calendar_year, p.prod_id;

Creating Materialized Join Views :

CREATE MATERIALIZED VIEW sales_by_month_by_state
     TABLESPACE example
     PARALLEL 4
     BUILD IMMEDIATE
     REFRESH COMPLETE
     ENABLE QUERY REWRITE
     AS SELECT t.calendar_month_desc, c.cust_state_province,
        SUM(s.amount_sold) AS sum_sales
        FROM times t, sales s, customers c
        WHERE s.time_id = t.time_id AND s.cust_id = c.cust_id
        GROUP BY t.calendar_month_desc, c.cust_state_province;

 

Periodic Refresh of Materialized Views:

CREATE MATERIALIZED VIEW emp_data 
   PCTFREE 5 PCTUSED 60 
   TABLESPACE example 
   STORAGE (INITIAL 50K NEXT 50K)
   REFRESH FAST NEXT sysdate + 7 
   AS SELECT * FROM employees; 

 

Automatic Refresh Times for Materialized Views

CREATE MATERIALIZED VIEW all_customers
   PCTFREE 5 PCTUSED 60 
   TABLESPACE example 
   STORAGE (INITIAL 50K NEXT 50K) 
   USING INDEX STORAGE (INITIAL 25K NEXT 25K)
   REFRESH START WITH ROUND(SYSDATE + 1) + 11/24 
   NEXT NEXT_DAY(TRUNC(SYSDATE), 'MONDAY') + 15/24 
   AS SELECT * FROM sh.customers@remote 
         UNION
      SELECT * FROM sh.customers@local;