Materialized Views

Carl Dudley University of Wolverhampton, UK Oracle ACE Director UKOUG Committee carl.dudley@wlv.ac.uk MaterializedViews

Introduction Working with Oracle since 1986 Oracle DBA - OCP Oracle7, 8, 9, 10 Oracle DBA of the Year – 2002 Oracle ACE Director Regular Presenter at Oracle Conferences Consultant and Trainer Technical Editor for a number of Oracle texts UK Oracle User Group Director Member of IOUC Day job – University of Wolverhampton, UK

Topics Materialized View Features and Syntax Query Rewrite Refreshing Materialized Views Nested Materialized Views Implementing Constraints via Materialized Views Fast and Complete Refresh Performance Multiple Materialized Views Estimating the Size of Materialized Views

Materialized View Features • Data Warehouses typically contain a few very large tables • Joins and aggregate queries can be very expensive • MVs often contain summary tables of aggregations of a table's data • Pre-calculated results enhance query performance • Usually defined as aggregate views and/or join views • For example, a view containing sums of salaries by job within department • Sometimes act as replica snapshots of tables on remote databases • Not covered in this presentation

Materialized Views Features (continued) • Not normally accessed directly • Queries on base tables are automatically re-written to use MVs • MVs can be nested • MVs built on other materialized views • Transparent to SQL applications • Can be created or dropped without affecting the validity of SQL statements • Consume storage space • Contents must be updated whenever underlying detail tables are modified

Creating Materialized Views CREATE MATERIALIZED VIEW emv BUILD IMMEDIATE REFRESH COMPLETE ON DEMAND AS SELECT deptno ,SUM(sal) FROM emp GROUP BY deptno; • This view is populated with data upon creation • To keep the view synchronised with emp, it must be completely refreshed • This will be done manually on demand (the default) • Queries against emp will not be re-written to use the view (default)

Creating Materialized Views (continued) CREATE MATERIALIZED VIEW emv BUILD DEFERRED REFRESH FAST ON COMMIT ENABLE QUERY REWRITE AS SELECT deptno ,SUM(sal) ,COUNT(*) ,COUNT(sal) FROM emp GROUP BY deptno; • Not populated with data until first refresh action takes place • The first refresh must be a complete refresh • To keep the view synchronised, it will undergo fast refresh operations • Refresh happens every time a transaction on emp commits • Queries against emp will be re-written to use the view • Unless the parameter QUERY_REWRITE_ENABLED is set to false

Query Rewrite • The optimizer compares costs when deciding to use a materialized view • Query is rewritten to use the materialized view • Underlying base tables are not used and performance is normally improved • A few carefully chosen MVs can support a large range of queries

Query Rewrite • Transformation of a query based on tables to an equivalent query based on one or more materialized views • Several factors affect whether query rewrite will occur • Enabling or disabling query rewrite can be achieved via the • CREATE or ALTER statement for materialized views • REWRITE and NOREWRITE hints in individual SQL statements SELECT /* +NOREWRITE */... • The REWRITE_OR_ERROR hint will prevent the processing of a statement if it is not rewritten • Rewrite integrity levels • Some levels allow the use of stale data • Dimensions and constraints • Outside the scope of this talk but details are in the Oracle docs

Controlling Accuracy of Query Rewrite with QUERY_REWRITE_INTEGRITY ALTER SESSION SET QUERY_REWRITE_INTEGRITY = ENFORCED|TRUSTED|STALE_TOLERATED; • Only the ENFORCED level guarantees totally accurate results • MVs will not be used if they are not refreshed after the base table changes

Checking on the MV Situation • DML on underlying objects causes NEEDS_COMPILE when REFRESH ON DEMAND is specified • A refresh or compile fixes the state SELECT mview_name,refresh_mode,staleness,compile_state FROM user_mviews MVIEW_NAME REFRESH_MODE STALENESS COMPILE_STATE ------------------------ ------------ ------------- ------------- CAL_MONTH_SALES_MV DEMAND NEEDS_COMPILE NEEDS_COMPILE FWEEK_PSCAT_SALES_MV DEMAND STALE VALID SUM_SALES_PSCAT_WEEK_MV COMMIT FRESH VALID ALTER MATERIALIZED VIEW cal_month_sales_mv COMPILE; SELECT mview_name,refresh_mode,staleness,compile_state FROM user_mviews; MVIEW_NAME REFRESH_MODE STALENESS COMPILE_STATE ------------------------ ------------ ------------- ------------- CAL_MONTH_SALES_MV DEMAND STALE VALID FWEEK_PSCAT_SALES_MV DEMAND STALE VALID SUM_SALES_PSCAT_WEEK_MV COMMIT FRESH VALID

Query Rewrite Performance CREATE MATERIALIZED VIEW sales_prod_mv BUILD IMMEDIATE ENABLE QUERY REWRITE AS SELECT prod_id ,SUM(amount_sold) FROM sales GROUP BY prod_id; Sample MV SELECT prod_id ,SUM(amount_sold) FROM sales GROUP BY prod_id; Sample query

Observing Query Rewrite • When the view is not present, the base table is scanned Elapsed time : 16.21 seconds • Queries may be rewritten to use the materialized view SELECT prod_id ,SUM(amount_sold) FROM sales GROUP BY prod_id; Elapsed time :0.03 seconds ----------------------------------------------------------------- |Id | Operation | Name |Rows | Bytes | Cost(%CPU) | ----------------------------------------------------------------- | 0 | SELECT STATEMENT | | 73 | 657 | 1182 (17)| | 1 | HASH GROUP BY | | 73 | 657 | 1182 (17)| | 2 | TABLE ACCESS FULL| SALES | 932K| 8196K| 1038 (5)| ----------------------------------------------------------------- --------------------------------------------------------------------------- |Id | Operation | Name |Rows|Bytes | Cost(%CPU)| --------------------------------------------------------------------------- | 0| SELECT STATEMENT | | 10| 1872 | 3 (0)| | 1| MAT_VIEW REWRITE ACCESS FULL| SALES_PROD_MV| 72| 1872 | 3 (0)| ---------------------------------------------------------------------------

Did Query Rewrite Occur and Why? • Query rewrite occurs transparently • Check for query rewrite • AUTOTRACE • dbms_mview.explain_rewrite • dbms_mview.explain_rewrite • Gives reasons WHY the rewrite did not occur • The rules governing this are extremely complex • Populates the rewrite_table with information • Which materialized views will be used • Comparative costings of the rewritten and un-rewritten query • Each account must have its own rewrite_table • Created by running the utlxrw script in the admin directory

Explaining the rewrite: Example One BEGIN DBMS_MVIEW.EXPLAIN_REWRITE ('SELECTprod_id,SUM(amount_sold) FROM sales GROUP BY prod_id'); END; / SELECT message ,original_cost ,rewritten_cost FROM rewrite_table; MESSAGE ORIGINAL_COST REWRITTEN_COST ------------------------------------------------------------ ------------- -------------- QSM-01009: materialized view,prod_sum_mv, matched query text 180 3 • An efficient rewrite occurs because of the exact match of the query and the definition of the view • Most rewrites are more complex • Aliases can be used for MV columns to allow exact match with table columns CREATE MATERIALIZED VIEW emv (e_deptno, d_deptno) ENABLE QUERY REWRITE AS SELECT e.deptno, d.deptno...

Explaining the rewrite: Example Two BEGIN DBMS_MVIEW.EXPLAIN_REWRITE ('SELECTprod_id,AVG(amount_sold) FROM sales GROUP BY prod_id'); END; / SELECT message ,original_cost ,rewritten_cost FROM rewrite_table; MESSAGE ORIGINAL_COST REWRITTEN_COST ------------------------------------------ ------------- -------------- QSM-01086: dimension(s) not present or not 0 0 used in ENFORCED integrity mode QSM-01065: materialized view, prod_sum_mv, 0 0 Cannot compute measure, AVG, in the query • Query rewrite cannot occur for the given reasons • No costs are calculated

Undocumented Query Rewrite Parameters COL ksppinm FORMAT a40 COL ksppstvl FORMAT a40 SELECT ksppinm,ksppstvl FROM x$ksppi i,x$ksppcv v WHERE i.indx = v.indx AND i.ksppinm like '%rewrite%‘ AND SUBSTR(ksppinm,1,1) = '_'; KSPPINM KSPPSTVL ------------------------------------ ------------ _bt_mmv_query_rewrite_enabled TRUE _pre_rewrite_push_pred TRUE _union_rewrite_for_gs YES_GSET_MVS _query_rewrite_setopgrw_enable TRUE _force_rewrite_enable FALSE _query_mmvrewrite_maxpreds 10 _query_mmvrewrite_maxintervals 5 _query_mmvrewrite_maxinlists 5 _query_mmvrewrite_maxdmaps 10 _query_mmvrewrite_maxcmaps 20 _query_mmvrewrite_maxregperm 512 _query_mmvrewrite_maxmergedcmaps 50 _query_mmvrewrite_maxqryinlistvals 500 _enable_query_rewrite_on_remote_objs TRUE KSPPINM KSPPSTVL -------------------------- -------- _explain_rewrite_mode FALSE _query_rewrite_or_error FALSE _query_cost_rewrite TRUE _query_rewrite_2 TRUE _query_rewrite_1 TRUE _query_rewrite_fudge 90 _query_rewrite_expression TRUE _query_rewrite_jgmigrate TRUE _query_rewrite_fpc TRUE _query_rewrite_drj TRUE _query_rewrite_maxdisjunct 257 _query_rewrite_vop_cleanup TRUE _mmv_query_rewrite_enabled TRUE

Refreshing Materialized Views • If data in base tables change, MVs need to be refreshed in one of two ways • On transaction commit (REFRESH ON COMMIT) • MV kept up to date but performance impact on base table transactions 2. On demand (REFRESH ON DEMAND) • Requires a procedure to be invoked • The procedure can take an argument to specify the type of refresh COMPLETE - complete refresh by repopulating FAST - incrementally applies changes to the view FORCE - fast refresh if possible, if not it forces a complete refresh dbms_mview.refresh('emp_mv1')

Refreshing Materialized Views • Refresh can be accelerated with ATOMIC_REFRESH set to 'FALSE' • MV is truncated rather then deleted (default value is TRUE on 10g and up) • Less undo and redo but MV unavailable during refresh • Allows parallel refresh • Fast Refresh can be accelerated if MV logs are created 'WITH COMMIT SCN' • Snapshots do not have to be built during the refresh • Use is made of all_summap view • Can also set NOLOGGING for the materialized view logs

Materialized View Logs • Materialized view logs are required for fast refresh • Defined using CREATE MATERIALIZED VIEW LOG on the base table • Not created on the materialized view • Each inserted and deleted row on the base table creates a row in the log • An update may generate two rows in the log for old and new values • Normally need to specify the ROWID clause • Logs that support aggregate materialized views must also contain: • Every column in the table that is referenced in the materialized view • The INCLUDING NEW VALUES clause • Name of the log is mlog$_<base_table_name> • Specify SEQUENCE if the table is expected to have a mix of inserts/direct-loads, deletes, and updates CREATE MATERIALIZED VIEW LOG ON emp WITH ROWID (deptno,sal) INCLUDING NEW VALUES;

Tuning Materialized Views for Fast Refresh and Query Rewrite • dbms_advisor.tune_mview helps optimize CREATE MATERIALIZED VIEW statements • Shows how to implement materialized views and any required logs • The output statements for ‘IMPLEMENTATION’ include: • CREATE MATERIALIZED VIEW LOG statements • Creates any missing materialized view logs required for fast refresh • ALTER MATERIALIZED VIEW LOG FORCE statements • Fixes any materialized view logs for maybe fast refresh • One or more CREATE MATERIALIZED VIEW statements • Could suggest just adding required columns • For example, add ROWID column for materialized join view and add aggregate column for materialized aggregate view

Tuning Materialized Views – Using the Advisor VARIABLE task_cust_mv VARCHAR2(30); VARIABLE create_mv_ddl VARCHAR2(4000); BEGIN :task_cust_mv := 'emp_mv1'; :create_mv_ddl := 'CREATE MATERIALIZED VIEW emp_mv1 REFRESH FAST ENABLE QUERY REWRITE AS SELECT deptno,sum(sal) FROM emp GROUP BY deptno'; DBMS_ADVISOR.TUNE_MVIEW(:task_cust_mv, :create_mv_ddl); END; / Requires ADVISOR privilege • Tasks can be deleted EXEC dbms_advisor.delete_task('emp_mv1')

Tuning Materialized Views – Examining the Output • For the view to be fast refreshed • A log is recommended because the view will be fast refreshed • The view must include a count of the aggregate column and also a count of the rows SELECT statement FROM user_tune_mview WHERE task_name= :task_cust_mv AND script_type='IMPLEMENTATION'; STATEMENT ------------------------------------------------------------------ CREATE MATERIALIZED VIEW LOG ON "SCOTT"."EMP" WITH ROWID, SEQUENCE • "SAL","DEPTNO") INCLUDING NEW VALUES ALTER MATERIALIZED VIEW LOG FORCE ON "SCOTT"."EMP" ADD ROWID, SEQUENCE • "SAL","DEPTNO") INCLUDING NEW VALUES CREATE MATERIALIZED VIEW SCOTT.EMP_MV1 REFRESH FAST WITH ROWID ENABLE QUERY REWRITE AS SELECT SCOTT.EMP.DEPTNO C1, SUM("SCOTT"."EMP"."SAL") M1, COUNT("SCOTT"."EMP"."SAL") M2, COUNT(*) M3 FROM SCOTT.EMP GROUP BY • SCOTT.EMP.DEPTNO

Tuning Materialized Views – a Further Example BEGIN :task_cust_mv := 'emp_mv2'; :create_mv_ddl := 'CREATE MATERIALIZED VIEW emp_mv2 REFRESH FAST ENABLE QUERY REWRITE AS SELECT dept.deptno,dname,sum(sal) FROM emp,dept WHERE dept.deptno = emp.deptno GROUP BY dept.deptno,dname' DBMS_ADVISOR.TUNE_MVIEW(:task_cust_mv, :create_mv_ddl); END; /

Further Example Output SELECT statement FROM user_tune_mview WHERE task_name= :task_cust_mv AND script_type='IMPLEMENTATION'; STATEMENT -------------------------------------------------------------------- CREATE MATERIALIZED VIEW LOG ON "SCOTT"."DEPT" WITH ROWID, SEQUENCE ("DEPTNO","DNAME") INCLUDING NEW VALUES ALTER MATERIALIZED VIEW LOG FORCE ON "SCOTT"."DEPT" ADD ROWID, SEQUENCE ("DEPTNO","DNAME") INCLUDING NEW VALUES CREATE MATERIALIZED VIEW LOG ON "SCOTT"."EMP" WITH ROWID, SEQUENCE ("SAL","DEPTNO") INCLUDING NEW VALUES ALTER MATERIALIZED VIEW LOG FORCE ON "SCOTT"."EMP" ADD ROWID, SEQUENCE ("SAL","DEPTNO") INCLUDING NEW VALUES CREATE MATERIALIZED VIEW SCOTT.EMP_MV2 REFRESH FAST WITH ROWID ENABLE QUERY REWRITE AS SELECT SCOTT.DEPT.DNAME C1, SCOTT.DEPT.DEPTNO C2, SUM("SCOTT"."EMP"."SAL") M1, COUNT("SCOTT"."EMP"."SAL") M2, COUNT(*) M3 FROM SCOTT.EMP, SCOTT.DEPT WHERE SCOTT.DEPT.DEPTNO = SCOTT.EMP.DEPTNO GROUP BY SCOTT.DEPT.DNAME, SCOTT.DEPT.DEPTNO

Materialized View Capabilities • dbms_mview.explain_mview is used to explain the capabilities (query rewrite, refresh modes) of an MV EXECUTE dbms_mview.explain_mview ('emp_mv1','6') • The optional value '6' is a user supplied statement identifier • Places information in mv_capabilities_table • Has one row for each reason why a certain action is not possible • Needs to be built in each investigating account by executing the script utlxmv.sql found in the admin directory • The statement_id column can be used to fetch rows for a given MV

The mv_capabilities_table EXECUTE dbms_mview.explain_mview('emp_mv1','6') SELECT capability_name,possible,msgtxt FROM mv_capabilities_table WHERE statement_id = '6'; CAPABILITY_NAME P MSGTXT ----------------------------- - ----------------------------------------------- PCT N REFRESH_COMPLETE Y REFRESH_FAST Y REWRITE Y PCT_TABLE N relation is not a partitioned table REFRESH_FAST_AFTER_INSERT Y REFRESH_FAST_AFTER_ONETAB_DML N SUM(expr) without COUNT(expr) REFRESH_FAST_AFTER_ONETAB_DML N COUNT(*) is not present in the select list REFRESH_FAST_AFTER_ANY_DML N mv log does not have sequence # REFRESH_FAST_AFTER_ANY_DML N see the reason why REFRESH_FAST_AFTER_ONETAB_DML is disabled REFRESH_FAST_PCT N PCT is not possible on any of the detail tables in the materialized view REWRITE_FULL_TEXT_MATCH Y REWRITE_PARTIAL_TEXT_MATCH Y REWRITE_GENERAL Y REWRITE_PCT N general rewrite is not possible or PCT is not possible on any of the detail tables PCT_TABLE_REWRITE N relation is not a partitioned table

Restrictions on Fast Refresh on MVs • Joins… • ROWIDs of all tables in FROM list must appear in the SELECT list of the query • Materialized view logs must exist on all tables involved in the join • Aggregates… • Only SUM, COUNT,AVG,STDDEV,VARIANCE,MIN and MAX are supported for fast refresh • COUNT(*) must be included in the view • For each aggregate such as AVG(expr), the corresponding COUNT(expr) must be present There are many more special case restrictions and rules

Fast Refresh on Commit sales sales_mv 25 47 25 47 25 47 build 62 62 Base table materialized view 62 62 mlog$_sales commit 62 Materialized view log • REFRESH COMPLETE ON COMMIT does not require logs • Very unlikely to be used

Bulk Loading – Impact on Fast Refresh • INSERT /*+ append */ • SQL*Loader DIRECT=TRUE • Improved performance as no data written to MV log • Much less redo and undo • Minimal tracking in all_sumdelta • Summary information with ROWID ranges

Flexing the Materialized Views with ON PREBUILT TABLE • MVs cannot be ALTERed • What if a column width change is required? • Could make use of ON PREBUILT TABLE to avoid MV rebuild CREATE TABLE j_g AS SELECT job, COUNT(*) ct FROM emp GROUP BY job; CREATE MATERIALIZED VIEW j_g ON PREBUILT TABLE AS SELECT job, COUNT(*) ct FROM emp GROUP BY job; OBJECT_ID DATA_OBJECT_ID OBJECT_NAME OBJECT_TYPE --------- -------------- ----------- ------------------- 86118 86118 J_G TABLE 86119 J_G MATERIALIZED VIEW DROP TABLE j_g; -- fails due to presence of MV DROP MATERIALIZED VIEW j_g; --succeeds but leaves table intact ALTER TABLE j_g MODIFY job VARCHAR2(40); CREATE MATERIALIZED VIEW j_g ON PREBUILT TABLE AS SELECT job, COUNT(*) ct FROM emp GROUP BY job; • Can also help when fixing 'out of sync' errors with logs and base tables • ORA-12034: materialized view log younger than last refresh

NestedMaterializedViews Base table 1 Base table 2 Base table 3 Join Materialized View Aggregate Materialized View 1 Aggregate Materialized View 2 Aggregate Materialized View 3 Aggregate Materialized View 4 Nested Views

Prod_id Cust_id Amt_sold 10 237 22.05 14 120 9.23 10 194 3.87 sales Sales products Prod_id Cust_id Amt_sold 10 237 22.05 14 120 9.23 10 194 3.87 10 105 110.83 Prod_id Prod_name 10 Bolt 14 Pin prod_sales (master) Prod_name Cust_id Amt_sold Bolt 237 22.05 Pin 120 9.23 Bolt 194 3.87 Prod_name Cust_id Amt_sold Bolt 237 22.05 Pin 120 9.23 Bolt 194 3.87 Bolt 105 110.83 Refresh the master Prod_name SUM(Amt_sold) Bolt 136.75 Pin 9.23 sum_prod_sales (nested) Fresh Prod_name SUM(Amt_sold) Bolt 25.92 Pin 9.23 Refresh nested view Refreshing Nested MVs Update the sales table Fresh Stale Stale • DBMS_MVIEW.REFRESH_DEPENDENT with NESTED = TRUE refreshes all views in a hierarchy that depend on one or more specified base tables

Create a master materialized view CREATE MATERIALIZED VIEW master_nest_mv BUILD IMMEDIATE ENABLE QUERY REWRITE AS SELECT p.prod_id, p.prod_name, c.channel_id, c.channel_desc FROM sales s, products p, channels c WHERE s.prod_id = p.prod_id AND s.channel_id = c.channel_id; 3. The master materialized view also requires a log containing ROWID, each column in the materialized view itself, and all new values CREATE MATERIALIZED VIEW LOG ON master_nest_mv WITH ROWID (prod_id, prod_name, channel_id, channel_desc) INCLUDING NEW VALUES; Nested MVs in Query Rewrite 2. Every master object requires a log containing the ROWID CREATE MATERIALIZED VIEW LOG ON sales WITH ROWID; CREATE MATERIALIZED VIEW LOG ON products WITH ROWID; CREATE MATERIALIZED VIEW LOG ON channels WITH ROWID;

4. Create the nested MV based on the master MV CREATE MATERIALIZED VIEW nested_prod_chan_mv BUILD IMMEDIATE ENABLE QUERY REWRITE AS SELECT channel_desc, prod_name, COUNT(prod_id) FROM master_nest_mv GROUP BY channel_desc, prod_name; Nested MVs in Query Rewrite (continued) • AUTOTRACE shows evidence of use but does not show the hierarchy ----------------------------------------------------------------------------------- |Id| Operation |Name |Rows|Bytes|Cost(%CPU)| Time | ----------------------------------------------------------------------------------- | 0|SELECT STATEMENT | | 225| 9450| 3 (0) |00:00:01| | 1|MAT_VIEW REWRITE ACCESS FULL|NESTED_PROD_CHAN_MV| 225| 9450| 3 (0) |00:00:01| -----------------------------------------------------------------------------------

Nested MVs in Query Rewrite (continued) BEGIN DBMS_MVIEW.EXPLAIN_REWRITE( 'SELECT c.channel_desc, p.prod_name FROM sales s, products p, channels c WHERE s.prod_id = p.prod_id AND c.channel_id = s.channel_id GROUP BY channel_desc, prod_name' ,'NESTED_PROD_CHAN_MV'); END; / SELECT message FROM rewrite_table; MESSAGE ----------------------------------------------------------------- QSM-01151: query was rewritten QSM-01033: query rewritten with materialized view, NESTED_PROD_CHAN_MV QSM-01336: the materialized view you specified (NESTED_PROD_CHAN_MV) was not used to rewrite the query QSM-01033: query rewritten with materialized view, MASTER_NEST_MV

EMPDEP ENAME OCCUPATION -------- ---------- WOODS CLERK JOHNSON MANAGER COX CLERK PITT CLERK SPINK SALESMAN DRAPER MANAGER EMP EMP_V1 EMPNO JOB ----- -------- 7366 SALESMAN 7500 MANAGER 7902 MANAGER 7566 CLERK 7934 SALESMAN JOB -------- SALESMAN MANAGER CLERK primary key foreign key Foreign Keys Referencing Non-unique Columns • Problem : • Need to enforce a foreign key constraint on the occupation column in the empdep table based on data in the emp table • Requires a reference to a non-unique column (job) in the emp table • Reference a materialized view carrying only unique values of job

Enforcing Foreign Keys Without Primary keys CREATE MATERIALIZED VIEW LOG ON emp WITH ROWID,PRIMARY KEY,SEQUENCE(job) INCLUDING NEW VALUES; CREATE MATERIALIZED VIEW emp_v1 REFRESH FAST ON COMMIT ENABLE QUERY REWRITE AS SELECT job,COUNT(*),COUNT(job) FROM emp GROUP BY job; ALTER MATERIALIZED VIEW emp_v1 ADD PRIMARY KEY (job); ALTER TABLE empdep ADD CONSTRAINT empdep_emp_v1 FOREIGN KEY (occupation) REFERENCES emp_v1; UPDATE empdep SET occupation = 'X'; ORA-02291: integrity constraint (SCOTT.EMPDEP_EMP_V1) violated - parent key not found job is not unique in emp job is unique in MV empdep must have only jobs in the emp table

Enforcing Complex Constraints with Materialized Views • Employee's appraisal date must be within their department's appraisal period EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO APP_DATE ----- ------ --------- ---- --------- ---- ---- ------ --------- 7369 SMITH CLERK 7902 17-DEC-80 800 20 15-MAR-11 7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30 15-APR-11 7521 WARD SALESMAN 7698 22-FEB-81 250 500 30 15-APR-11 7566 JONES MANAGER 7839 02-APR-81 2975 20 15 MAR-11 DEPTNO DNAME LOC START_APP_PERIOD END_APP_PERIOD ------ -------- -------- ---------------- -------------- 20 RESEARCH DALLAS 01-FEB-1101-MAY-11 30 SALES CHICAGO 01-MAR-1101-JUN-11 • MV which returns app_dates with departmental appraisal periods CREATE MATERIALIZED VIEW emp_dept_appr_mv REFRESH FAST ON COMMIT AS SELECT e.deptno ,e.app_date e_app_date ,d.start_app_period d_start_app_period ,d.end_app_period d_end_app_period ,e.ROWID e_rowid ,d.ROWID d_rowid FROM emp e ,dept d WHERE e.deptno = d.deptno constraint checked when changes to the emp table are committed

Enforcing Complex Constraints with Materialized Views (continued) • Prevent invalid appr_dates appearing in the view ALTER TABLE emp_dept_appr_mv ADD CONSTRAINT emp_dept_appr_mv_chk1 CHECK (e_app_date BETWEEN d_start_app_period AND d_end_app_period) DEFERRABLE; • Constraint is checked on commit rather than on update • Insert a row with an appr_date outside of the departmental period INSERT INTO emp (empno,deptno,app_date) VALUES(1, 20, '01-jan-2013'); • Constraint is not enforced until the commit (and refresh) takes place SQL> COMMIT; ORA-12048: error encountered while refreshing materialized View "EMP_DEPT_APPR_MV" ORA-02290: check constraint (emp_dept_appr_mv_chk1) violated

Testing Complex Constraint Checking with Materialized Views • Could lead to large numbers of rows in the MV when constraint is repeatedly NOT violated • Hence, simply reverse the MV condition to prevent valid data in the view • The MV never collects any data • Constraint prevents the invalid data • But the MV logs are still populated! AND NOT (e.app_date BETWEEN d.start_app_period AND d.end_app_period)

Indexes on Materialized Views • A single unique index is often created on an MV when it is built • If the MV is a grouped (aggregate) MV, the index is a function-based Btree and has a name of I_SNAP$_<mv_name> • Built on virtual RAW column(s) built into the MV • Number of columns in the index is equal to number of grouping columns • Names are similar to sys_nc00003$ CREATE MATERIALIZED VIEW sales_mv AS SELECT p.prod_subcategory, t.week_ending_day, SUM(s.amount_sold) AS sum_amount_sold FROM sales s, products p, times t WHERE s.time_id = t.time_id AND s.prod_id = p.prod_id GROUP BY p.prod_subcategory, t.week_ending_day generates two hidden indexed columns in the MV

Indexes on Grouped Materialized Views • Values in the hidden columns SELECT sys_nc00004$ ,sys_nc00005$ ,prod_subcategory ,week_ending_day FROM sales_mv; SYS_NC00004$ SYS_NC00005$ PROD_SUBCATEGORY WEEK_ENDING_DAY ------------------------ ---------------- ---------------- --------------- 43616D6572617300 77C6020101010100 Cameras 01-FEB-98 4D6F6E69746F727300 77C6031601010100 Monitors 22-MAR-98 4465736B746F702050437300 77C6030801010100 Desktop PCs 08-MAR-98 43616D636F72646572730077C6010B01010100Camcorders11-JAN-98 43616D636F726465727300 77C6011901010100 Camcorders 25-JAN-98 4163636573736F726965730077C6020101010100Accessories 01-FEB-98 4163636573736F7269657300 77C6030101010100 Accessories 01-MAR-98 486F6D6520417564696F0077C6010B01010100HomeAudio11-JAN-98 486F6D6520417564696F0077C6031601010100HomeAudio22-MAR-98 : : : : • 43 is the ASCII value of ‘C’ in hexadecimal

Indexes on Non-grouped Materialized Views • If base table has a PK and a one to one mapping of rows with the MV • A normal Btree index is built on the PK within the view • Oracle recommends building an index on any ROWID column in the MVand a concatenated index on all key columns of the base tables on the MV (for fast refresh) • If PK on the base table is explicitly named (e.g PK_EMP) • The normal Btree index on the MV is named PK_EMP1and supports a constraint named PK_EMP1 • If no primary key on the base table • No index is built on the MV Indexes are not built when the USING NO INDEX clause is used in MV creation

Managing Update and Refresh Operations • Tests performed on a 918443 row sales table having one MV in fast or complete refresh mode • Updates performed in batch mode (single update statement) on base table

Materialized Views