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

Comments