1 / 30

Materialized Views

Materialized Views. Materialized Views – Agenda. What is a Materialized View? Advantages and Disadvantages How Materialized Views Work Parameter Settings, Privileges, Query Rewrite Creating Materialized Views Syntax, Refresh Modes/Options, Build Methods Examples Dimensions What are they?

issac
Télécharger la présentation

Materialized Views

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Materialized Views Willie Albino May 15, 2003

  2. Materialized Views – Agenda • What is a Materialized View? • Advantages and Disadvantages • How Materialized Views Work • Parameter Settings, Privileges, Query Rewrite • Creating Materialized Views • Syntax, Refresh Modes/Options, Build Methods • Examples • Dimensions • What are they? • Examples Willie Albino May 15, 2003

  3. What is a Materialized View? • A database object that stores the results of a query • Marries the query rewrite features found in Oracle Discoverer with the data refresh capabilities of snapshots • Features/Capabilities • Can be partitioned and indexed • Can be queried directly • Can have DML applied against it • Several refresh options are available • Best in read-intensive environments Willie Albino May 15, 2003

  4. Advantages and Disadvantages • Advantages • Useful for summarizing, pre-computing, replicating and distributing data • Faster access for expensive and complex joins • Transparent to end-users • MVs can be added/dropped without invalidating coded SQL • Disadvantages • Performance costs of maintaining the views • Storage costs of maintaining the views Willie Albino May 15, 2003

  5. Database Parameter Settings • init.ora parameter • COMPATIBLE=8.1.0 (or above) • System or session settings • query_rewrite_enabled={true|false} • query_rewrite_integrity= {enforced|trusted|stale_tolerated} • Can be set for a session using • alter session set query_rewrite_enabled=true; • alter session set query_rewrite_integrity=enforced; • Privileges which must be granted to users directly • QUERY_REWRITE - for MV using objects in own schema • GLOBAL_QUERY_REWRITE - for objects in other schemas Willie Albino May 15, 2003

  6. Query Rewrite Details • query_rewrite_integrity Settings: • enforced – rewrites based on Oracle enforced constraints • Primary key, foreign keys • trusted – rewrites based on Oracle enforced constraints and known, but not enforced, data relationships • Primary key, foreign keys • Data dictionary information • Dimensions • stale_tolerated – queries rewritten even if Oracle knows the mv’s data is out-of-sync with the detail data • Data dictionary information Willie Albino May 15, 2003

  7. Query Rewrite Details (cont’d) • Query Rewrite Methods • Full Exact Text Match • Friendlier/more flexible version of text matching • Partial Text Match • Compares text starting at FROM clause • SELECT clause must be satisfied for rewrite to occur • Data Sufficiency • All required data must be present in the MV or retrievable through a join-back operation • Join Compatibility • All joined columns are present in the MV Willie Albino May 15, 2003

  8. Query Rewrite Details (cont’d) • Grouping Compatibility • Allows for matches in groupings at higher levels than those defined MV query • Required if both query and MV contain a GROUP BY clause • Aggregate Compatibility • Allows for interesting rewrites of aggregations • If SUM(x) and COUNT(x) are in MV, the MV may be used if the query specifies AVG(x) Willie Albino May 15, 2003

  9. Syntax For Materialized Views • CREATE MATERIALIZED VIEW <name> • TABLESPACE <tbs name> {<storage parameters>} • <build option> • REFRESH <refresh option> <refresh mode> • [ENABLE|DISABLE] QUERY REWRITE • AS • SELECT <select clause>; • The <build option> determines when MV is built • BUILD IMMEDIATE: view is built at creation time • BUILD DEFFERED: view is built at a later time • ON PREBUILT TABLE: use an existing table as view source • Must set QUERY_REWRITE_INTEGRITY to TRUSTED Willie Albino May 15, 2003

  10. Materialized View Refresh Options • Refresh Options • COMPLETE – totally refreshes the view • Can be done at any time; can be time consuming • FAST – incrementally applies data changes • A materialized view log is required on each detail table • Data changes are recorded in MV logs or direct loader logs • Many other requirements must be met for fast refreshes • FORCE – does a FAST refresh in favor of a COMPLETE • The default refresh option Willie Albino May 15, 2003

  11. Materialized View Refresh Modes • Refresh Modes • ON COMMIT – refreshes occur whenever a commit is performed on one of the view’s underlying detail table(s) • Available only with single table aggregate or join based views • Keeps view data transactionally accurate • Need to check alert log for view creation errors • ON DEMAND – refreshes are initiated manually using one of the procedures in the DBMS_MVIEW package • Can be used with all types of materialized views • Manual Refresh Procedures • DBMS_MVIEW.REFRESH(<mv_name>, <refresh_option>) • DBMS_MVIEW.REFRESH_ALL_MVIEWS() • START WITH [NEXT] <date> - refreshes start at a specified date/time and continue at regular intervals Willie Albino May 15, 2003

  12. Materialized View Example CREATE MATERIALIZED VIEW items_summary_mv ON PREBUILT TABLE REFRESH FORCE AS SELECT a.PRD_ID, a.SITE_ID, a.TYPE_CODE, a.CATEG_ID, sum(a.GMS) GMS, sum(a.NET_REV) NET_REV, sum(a.BOLD_FEE) BOLD_FEE, sum(a.BIN_PRICE) BIN_PRICE, sum(a.GLRY_FEE) GLRY_FEE, sum(a.QTY_SOLD) QTY_SOLD, count(a.ITEM_ID) UNITS FROM items a GROUP BY a.PRD_ID, a.SITE_ID, a.TYPE_CODE, a.CATEG_ID; ANALYZE TABLE item_summary_mv COMPUTE STATISTICS; Willie Albino May 15, 2003

  13. Materialized View Example (cont’d) -- Query to test impact of materialized view select categ_id, site_id, sum(net_rev), sum(bold_fee), count(item_id) from items where prd_id in ('2000M05','2000M06','2001M07','2001M08') and site_id in (0,1) and categ_id in (2,4,6,8,1,22) group by categ_id, site_id save mv_example.sql Willie Albino May 15, 2003

  14. Materialized View Example (cont’d) SQL> ALTER SESSION SET QUERY_REWRITE_INTEGRITY=TRUSTED; SQL> ALTER SESSION SET QUERY_REWRITE_ENABLED=FALSE; SQL> @mv_example.sql CATEG_ID SITE_ID SUM(NET_REV) SUM(BOLD_FEE) COUNT(ITEM_ID) -------- ------- ------------ ------------- -------------- 1 0 -2.35 0 1 22 0 -42120.87 -306 28085 Elapsed: 01:32:17.93 Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=HINT: FIRST_ROWS (Cost=360829 Card=6 Bytes=120) 1 0 SORT (GROUP BY) (Cost=360829 Card=6 Bytes=120) 2 1 PARTITION RANGE (INLIST 3 2 TABLE ACCESS (FULL) OF ‘ITEMS' (Cost=360077 Card=375154 Bytes=7503080) Willie Albino May 15, 2003

  15. Materialized View Example (cont’d) SQL> ALTER SESSION SET QUERY_REWRITE_ENABLED=TRUE; SQL> @mv_example.sql CATEG_ID SITE_ID SUM(NET_REV) SUM(BOLD_FEE) COUNT(ITEM_ID) -------- ------- ------------ ------------- -------------- 1 0 -2.35 0 1 22 0 -42120.87 -306 28085 Elapsed: 00:01:40.47 Execution Plan ---------------------------------------------------------------------------------------------- 0 SELECT STATEMENT Optimizer=HINT: FIRST_ROWS (Cost=3749 Card=12 Bytes=276) 1 0 SORT (GROUP BY) (Cost=3749 Card=12 Bytes=276) 2 1 PARTITION RANGE (INLIST) 3 2 TABLE ACCESS (FULL) OF ‘ITEMS_SUMMARY_MV' (Cost=3723 Card=7331 Bytes=168613) Willie Albino May 15, 2003

  16. Example of FAST REFRESH MV CREATE MATERIALIZED VIEW LOG ON ITEMS TABLESPACE MV_LOGS STORAGE(INITIAL 10M NEXT 10M) WITH ROWID; CREATE MATERIALIZED VIEW LOG ON CUSTOMERS TABLESPACE MV_LOGS STORAGE(INITIAL 1M NEXT 1M) WITH ROWID; CREATE MATERIALIZED VIEW cust_activity BUILD IMMEDIATE REFRESH FAST ON COMMIT AS SELECT u.ROWID cust_rowid, l.ROWID item_rowid, u.cust_id, u.custname, u.email, l.categ_id, l.site_id, sum(gms), sum(net_rev_fee) FROM customers u, items l WHERE u.cust_id = l.seller_id GROUP BY u.cust_id, u.custname, u.email, l.categ_id, l.site_id; Willie Albino May 15, 2003

  17. Getting Information About an MV Getting information about the key columns of a materialized view: SELECT POSITION_IN_SELECT POSITION, CONTAINER_COLUMN COLUMN, DETAILOBJ_OWNER OWNER, DETAILOBJ_NAME SOURCE, DETAILOBJ_ALIAS ALIAS, DETAILOBJ_TYPE TYPE, DETAILOBJ_COLUMN SRC_COLUMN FROM USER_MVIEW_KEYS WHERE MVIEW_NAME=‘ITEMS_SUMMARY_MV’; POS COLUMN OWNER SOURCE ALIAS TYPE SRC_COLUMN --- ---------- ----- -------- ----- ------ ----------- 1 PRD_ID TAZ ITEMS A TABLE PRD_ID 2 SITE_ID TAZ ITEMS A TABLE SITE_ID 3 TYPE_CODE TAZ ITEMS A TABLE TYPE_CODE 4 CATEG_ID TAZ ITEMS A TABLE CATEG_ID Willie Albino May 15, 2003

  18. Getting Information About an MV • Getting information about the aggregate columns of a materialized view: • SELECT POSITION_IN_SELECT POSITION, • CONTAINER_COLUMN COLUMN, • AGG_FUNCTION • FROM USER_MVIEW_AGGREGATES • WHERE MVIEW_NAME=‘ITEMS_SUMMARY_MV’; • POSITION COLUMN AGG_FUNCTION • -------- ----------------- ------------ • 6 GMS SUM • 7 NET_REV SUM • : : : • 11 QTY_SOLD SUM • 12 UNITS COUNT Willie Albino May 15, 2003

  19. Dimensions • A way of describing complex data relationships • Used to perform query rewrites, but not required • Defines hierarchical relationships between pairs of columns • Hierarchies can have multiple levels • Each child in the hierarchy has one and only one parent • Each level key can identify one or more attribute • Child join keys must be NOT NULL • Dimensions should be validated using the DBMS_OLAP.VALIDATE_DIMENSION package • Bad row ROWIDs stored in table: mview$_exceptions Willie Albino May 15, 2003

  20. Syntax For Creating A Dimension CREATE DIMENSION <dimension name> LEVEL [<level> IS <level_table.level_column> <level> IS <level_table.level_column>…] HIERARCHY <hierarchy_name> ( <child_level> CHILD OF <parent_level> <child_level> CHILD OF <parent_level>…] ATTRIBUTE <level> DETERMINES <dependent_column> <level> DETERMINES <dependent_column>,…); To validate a dimension: exec dbms_olap.validate_dimension(<dim_name>,<owner>,FALSE,FALSE); Willie Albino May 15, 2003

  21. Example of Creating A Dimension CREATE DIMENSION time_dim LEVEL CAL_DATE IS calendar.CAL_DATE LEVEL PRD_ID IS calendar.PRD_ID LEVEL QTR_ID IS calendar.QTR_ID LEVEL YEAR_ID IS calendar.YEAR_ID LEVEL WEEK_IN_YEAR_ID IS calendar.WEEK_IN_YEAR_ID HIERARCHY calendar_rollup (CAL_DATE CHILD OF PRD_ID CHILD OF QTR_ID CHILD OF YEAR_ID) HIERARCHY week_rollup (CAL_DATE CHILD OF WEEK_IN_YEAR_ID CHILD OF YEAR_ID) ATTRIBUTE PRD_ID DETERMINES PRD_DESC ATTRIBUTE QTR_ID DETERMINES QTR_DESC; Willie Albino May 15, 2003

  22. Example of Validating A Dimension SQL> exec dbms_olap.validate_dimension(‘time_dim’, USER, FALSE, FALSE); PL/SQL procedure successfully completed. SQL> select * from mview$_exceptions; no rows selected. -- Main cause of errors is a child level having multiple parents -- If above query returns rows, the bad rows can be found as follows: select * from calendar where rowid in (select bad_rowid from mview$_exceptions); Willie Albino May 15, 2003

  23. Example of Using Dimensions -- Step 1 of 4 -- Create materialized view (join-aggregate type) CREATE MATERIALIZED VIEW items_mv BUILD IMMEDIATE REFRESH ON DEMAND ENABLE QUERY REWRITE AS SELECT l.slr_id , c.cal_date, sum(l.gms) gms FROM items l, calendar c WHERE l.end_date=c.cal_date GROUP BY l.slr_id, c.cal_date; Willie Albino May 15, 2003

  24. Example of Using Dimensions (cont’d) -- Step 2 of 4: (not really required, for demonstration only) -- Execute query based on “quarter”, not “date”, without a time dimension -- Note that the detail tables are accessed SQL> select c.qtr_id, sum(l.gms) gms 2 from items l, calendar c 3 where l.end_date=c.cal_date 4 group by l.slr_id, c.qtr_id; Execution Plan ---------------------------------------------------------- SELECT STATEMENT Optimizer=CHOOSE (Cost=16174 Card=36258 Bytes=1160256) SORT (GROUP BY) (Cost=16174 Card=36258 Bytes=1160256) HASH JOIN (Cost=81 Card=5611339 Bytes=179562848) TABLE ACCESS (FULL) OF ’CALENDAR' (Cost=2 Card=8017 Bytes=128272) TABLE ACCESS (FULL) OF ’ITEMS' (Cost=76 Card=69993 Bytes=1119888) Willie Albino May 15, 2003

  25. Example of Using Dimensions (cont’d) -- Step 3 of 4: Create time dimension (see slide #21 for SQL) @cr_time_dim.sql Dimension Created -- Step 4 of 4: Rerun query based on “quarter” with time dimension SQL> select c.qtr_id, sum(l.gms) gms 2 from items l, calendar c 3 where l.end_date=c.cal_date 4 group by l.slr_id, c.qtr_id; Execution Plan ---------------------------------------------------------- SELECT STATEMENT Optimizer=CHOOSE (Cost=3703 Card=878824 Bytes=44820024) SORT (GROUP BY) (Cost=3703 Card=878824 Bytes=44820024) HASH JOIN (Cost=31 Card=878824 Bytes=44820024) VIEW (Cost=25 Card=8017 Bytes=128272) SORT (UNIQUE) (Cost=25 Card=8017 Bytes=128272) TABLE ACCESS (FULL) OF ‘CALENDAR’ (Cost=2 Card=8017 Bytes=128272) TABLE ACCESS (FULL) OF ‘ITEMS_MV’ (Cost=3 Card=10962 Bytes=383670) Willie Albino May 15, 2003

  26. Summary • Materialized Views • reduce system cpu/io resource requirements by pre-calculating and storing results of intensive queries • allow for the automatic rewriting of intensive queries • are transparent to the application • have storage/maintenance requirements • can understand complex data relationships • can be refreshed on demand or on a schedule • Dimensions • allow you to “tell” Oracle about complex data relationships which can be used to rewrite queries Willie Albino May 15, 2003

  27. References • Using Oracle9i Materialized Views (Technet Oracle By Example) • http://technet.oracle.com/products/oracle9i/htdocs/9iober2/obe9ir2/obe-dwh/html/mv/mv.htm • Oracle Expert-One-On-One – Thomas Kyte • The Secrets of Materialized Views • http://www.akadia.com/services/ora_materialized_views.html • OLAP DB-Design with Dimensions • http://www.akadia.com/services/ora_olap_dimensions.html • The Secrets of Dimensions • http://www.akadia.com/services/ora_dimensions.html Willie Albino May 15, 2003

  28. Willie Albino May 15, 2003

  29. Requirements for FAST REFRESH Willie Albino May 15, 2003

  30. Rqmts For FAST REFRESH (cont’d) Willie Albino May 15, 2003

More Related