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';