Materialized View
Benefits of Materialized Views
Ø Less physical reads
Ø Less writes
Ø Decreased CPU consumption
Ø Markedly faster response times
SQL> grant materialized view to scott;
Grant succeeded.
SQL> conn scott/tiger;
Connected.
SQL> select d.dname,sum(e.sal) dept_salary
from emp e,dept d
where e.deptno=d.deptno
group by d.dname;
DNAME DEPT_SALARY
-----------------------------------------------
ACCOUNTING 8750
RESEARCH 10875
SALES 9400
SQL> create materialized view mv_dept_salary
refresh on commit
as
select d.dname, sum(e.sal) dept_salary
from emp e, dept d
where e.deptno=d.deptno
group by d.dname;
Materialized view created.
SQL> desc mv_dept_salary;
Name Null? Type
----------------------------------------- -------- -------------
DNAME VARCHAR2(14)
DEPT_SALARY NUMBER
SQL> select * from mv_dept_salary ;
DNAME DEPT_SALARY
-----------------------------------------------
ACCOUNTING 8750
RESEARCH 10875
SALES 9400
SQL> update emp set sal=sal+10;
14 rows updated.
SQL> select * from mv_dept_salary ;
DNAME DEPT_SALARY
---------------------------------------------
ACCOUNTING 8750
RESEARCH 10875
SALES 9400
SQL> commit;
Commit complete.
SQL> select * from mv_dept_salary ;
DNAME DEPT_SALARY
--------------------------------------------
ACCOUNTING 8780
RESEARCH 10925
SALES 9460
Another Example:
STEP 1: CREATE A TABLE,INSERT SOME TEST DATA,ADD A PRIMARY KEY.
SQL> create table test
(id number,
name varchar2(10));
Table created.
SQL> insert into test values(1,'alex');
1 row created.
SQL> insert into test values(2,'robin');
1 row created.
SQL> commit;
Commit complete.
SQL> alter table test add primary key(id);
Table altered.
STEP 2: CREATE A MATERIALIZED VIEW BASED ON THIS THBLE.
SQL> create materialized view test_view
Refresh on commit
As select * from test;
Materialized view created.
STEP 3: QUERY THE MTERIALIZED VIEW.
SQL> select * from test_view;
ID NAME
---------- ----------
1 alex
2 robin
STEP 4: MODIFY THE BASE TABLE DATA.
SQL> insert into test values(3,'alin');
1 row created.
SQL> commit;
Commit complete.
STEP 5: SEE DATA IN THE MATERIALIZED VIEW ALSO MODIFIED.
SQL> select * from test_view;
ID NAME
---------- ----------
1 alex
2 robin
3 alin
Materialized View Refresh With Time:
SQL> CREATE MATERIALIZED VIEW MV_TEST1
REFRESH FORCE
START WITH SYSDATE
NEXT SYSDATE+1/24
AS
select d.dname, sum(e.sal) dept_salary
from emp e, dept d
where e.deptno=d.deptno
group by d.dname;
Materialized view created.
Above Command Will refresh the materialized view every hour.
Altering a Materialized View:
SQL> ALTER MATERIALIZED VIEW MV_TEST1
REFRESH FORCE
START WITH SYSDATE
NEXT SYSDATE+(1/24)/60;
Materialized view altered.
I have altered the materialized the view and make it refresh every minute.
Dropping a Materialized View:
SQL> drop materialized view mv_dept_salary;
Materialized view dropped.
Check Created Materialized View:
SQL> SELECT mview_name, refresh_mode, refresh_method,
last_refresh_type, last_refresh_date
FROM user_mviews;
Check Created Materialized View:
SQL> SELECT mview_name, refresh_mode, refresh_method,last_refresh_type, last_refresh_date
FROM user_mviews;
Or
SELECT owner,mview_name, refresh_mode, refresh_method,last_refresh_type, last_refresh_date
FROM all_mviews mv
WHERE owner ='OWNER_NAME';
To check how long Materialized View Refresh Run:
SELECT mview_name,last_refresh_date "START_TIME",
CASE
WHEN fullrefreshtim <> 0 THEN
LAST_REFRESH_DATE + fullrefreshtim/60/60/24
WHEN increfreshtim <> 0 THEN
LAST_REFRESH_DATE + increfreshtim/60/60/24
ELSE
LAST_REFRESH_DATE
END "END_TIME",
fullrefreshtim "Full Refresh Time in Seconds" ,
increfreshtim "Fast Refresh Time in Seconds"
FROM all_mview_analysis
WHERE owner='OWNER_NAME';