Materialized view for Data Warehouse

What is materialized view?1. Materialized view is normal database object like “table,index”2. It is basically use for Data warehouse or Replication purpose.3. Snapshot is synonym for materialized view.4. A materialized view can be stored in the same database as its base tables or in a different database5. A materialized view provides access to table data by storing the results of a query in a separate schema object. Unlike an ordinary view, which does not take up any storage space or contain any data, a materialized view contains the rows resulting from a query against one or more base tables or views.6. A materialized view improve response time through query rewrite or reduce execution time.Note: Query rewrite is a mechanism where Oracle or applications from the end user or database transparently improve query response time, by automatically rewriting the SQL query to use the materialized view instead of accessing the original tables. Query rewrites are particularly useful in a data warehouse environment.What is syntax for materialized view?Create materialized view “materialized_view_name”Build [immediate / deferred]Refresh [fash [on commit / demand]]Enable query rewriteAs Sql statement/Now Details explanation about syntax 1. Create materialized view “name’I hope it is understood because self explanatory2. Build [immediate / deferred]1. Build immediate -- > materialized view populate with data on creation time from tables.2. Build deferred --- > materialized view not populate with data on creation time, we have to populate later.Eg: With build immediatecreate materialized view mymview1build immediateasselect ename,sum(sal) from emp group by ename;SQL> select count(*) from emp;COUNT(*)----------14Note: materialized view populate with data on creation time.With build deferredcreate materialized view mymview2build deferredasselect ename,sum(sal) from emp group by ename;SQL> select count(*) from mymview2;COUNT(*)----------0Note: materialized view not populate with data on creation time, we have to populate manually through DBMS_MVIEW.RERESH package.3. Refresh [fast [on commit / demand]]Refresh is method for populate materialized view with data whenever made changes in their base tables.There are five refresh method for materialized view1. refresh fast (materialized view log needed)2. refresh on commit3. refresh on demandWhat is materialized view log?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.Each materialized view log is associated with a single master table. The materialized view log resides in the same database and schema as its master table.Eg:With refresh on commitSQL> select count(*) from emp;COUNT(*)----------14SQL> create materialized view MYVIEW12 build immediate3 refresh on commit4 enable query rewrite5 as6 select ename, sum(sal) from emp group by ename;Materialized view created.SQL> select count(*) from myview1;COUNT(*)----------14SQL> insert into emp (empno,ename,sal) values(1754,'TAJ',2500);1 row created.SQL> commit;Commit complete.SQL> select count(*) from myview1;COUNT(*)----------15With refresh on commitSQL> select count(*) from emp;COUNT(*)----------15SQL> create materialized view MYVIEW52 build immediate3 refresh on demand4 enable query rewrite5 as6 select ename,sum(sal) from emp group by ename;Materialized view created.SQL> select count(*) from myview5;COUNT(*)----------15SQL> insert into emp (empno,ename,sal)values(1100,'xyz',1000);1 row created.SQL> commit;Commit complete.SQL> select count(*) from emp;COUNT(*)----------16SQL> select count(*) from myview5;COUNT(*)----------15SQL> exec dbms_mview.refresh('MYVIEW5');PL/SQL procedure successfully completed.SQL> select count(*) from myview5;COUNT(*)----------16Whenever any changes made in base tables and perform commit then materialized view refreshed.4. Enable query rewriteA materialized view is like a query with a result that is materialized and stored in a table. When a user query is found compatible with the query associated with a materialized view, the user query can be rewritten in terms of the materialized view. This technique improves the execution of the user query, because most of the query result has been precomputed. The query transformer looks for any materialized views that are compatible with the user query and selects one or more materialized views to rewrite the user query. The use of materialized views to rewrite a query is cost-based. That is, the query is not rewritten if the plan generated without the materialized views has a lower cost than the plan generated with the materialized views.Note: query_rewrite_enabled=true AND query_rewrite_integrity=enforced must be set to use query rewrite feature5. How Materialized view improve query performance.Let’s check with demo 1. Create Big Table :)SQL> create table big2 as select * from all_objects3 union all4 select * from all_objects5 union all6 select * from all_objects7 union all8 select * from all_objects9 union all10 select * from all_objects;Table created.SQL> select count(*) from big;COUNT(*)----------1889952. Now execute below query and check query statisticsSQL> alter system flush shared_pool;System altered.SQL> set autotrace traceonlySQL> set timing onSQL> select owner,count(*) from big group by owner;12 rows selected.Elapsed: 00:00:00.01Execution Plan----------------------------------------------------------0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=624 Card=188024 Bytes=3196408)1 0 SORT (GROUP BY) (Cost=624 Card=188024 Bytes=3196408)2 1 TABLE ACCESS (FULL) OF 'BIG' (TABLE) (Cost=598 Card=188024 Bytes=3196408)Statistics----------------------------------------------------------957 recursive calls0 db block gets2844 consistent gets---- > too big :)0 physical reads0 redo size1028 bytes sent via SQL*Net to client504 bytes received via SQL*Net from client2 SQL*Net roundtrips to/from client10 sorts (memory)0 sorts (disk)12 rows processed3. Now Create materialized view and try SQL> create materialized view MYVIEWBIG2 build immediate3 refresh on commit4 enable query rewrite5 as6 select owner,count(*) from big group by owner;Materialized view created.SQL> alter system flush shared_pool;System altered.SQL> set autotrace traceonlySQL> set timing onSQL> select owner,count(*) from big group by owner;12 rows selected.Elapsed: 00:00:00.01Execution Plan----------------------------------------------------------0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=3 Card=12 Bytes=360)1 0 MAT_VIEW REWRITE ACCESS (FULL) OF 'MYVIEWBIG' (MAT_VIEW REWRITE) (Cost=3 Card=12 Bytes=360)Statistics----------------------------------------------------------7356 recursive calls0 db block gets1313 consistent gets --- > just less then half :) 0 physical reads0 redo size1028 bytes sent via SQL*Net to client504 bytes received via SQL*Net from client2 SQL*Net roundtrips to/from client49 sorts (memory)0 sorts (disk)12 rows processed4. What Privileges Need for materialized view ?To create a materialized view in your own schema, you must have the CREATE MATERIALIZED VIEW privilege and the SELECT privilege to any tables referenced that are in another schema. To create a materialized view in another schema, you must have the CREATE ANY MATERIALIZED VIEW privilege and the owner of the materialized view needs SELECT privileges to the tables referenced if they are from another schema. Moreover, if you enable query rewrite on a materialized view that references tables outside your schema, you must have the GLOBAL QUERY REWRITE privilege or the QUERY REWRITE object privilege on each table outside your schema.5. How to check Status of a Materialized View?SQL> select mview_name,query,rewrite_enabled,refresh_mode,build_mode,staleness,compile_state2 from user_mviews;MVIEW_NAME QUERY R REFRES BUILD_MOD STALENESS COMPILE_ST---------- -------------------- - ------ --------- ---------- ----------MYBIG select owner, count( Y DEMAND IMMEDIATE NEEDS_COMP NEEDS_COMP*) from big group by ILE ILEownerMYMVIEW2 select ename,sum(sal N DEMAND DEFERRED UNUSABLE NEEDS_COMP) from emp group by ILEenameMYVIEW5 select ename,sum(sal Y DEMAND IMMEDIATE FRESH VALID) from emp group byenameMYMVIEW3 select ename,sum(sal Y COMMIT IMMEDIATE UNUSABLE VALID) from emp group byenameMYVIEW1 select ename, sum(sa Y COMMIT IMMEDIATE FRESH VALIDl) from emp group byExplanation:MVIEW_NAME -------- > Materialized View nameQUERY ------------- > Query that defines the materialized viewREFRESH_ENABLED --- > If query_rewrite_enabled=TRUE then value is “Y” otherwise “N”REFRESH_MODE ------ > DEMAND, COMMIT, NEVERBUILD_MODE -------- > IMMEDIATE, DEFERREDSTALENESS --------- > Relationship between the contents of the materialized view and the contents of the materialized view's masters.COMPILE_STATE ----- > Validity of the materialized view with respect to the objects upon which it dependsVALID -> No objects depends has changed since the last validationNEED_COMPILE -> Some objects depends materialized view has changed an “alter materialized view … compile;” statement is required to validate this materialized view