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;

Reactions:
 
You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response.
0 Responses
Leave a Reply

Recent Comments

Subscribe