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