1 / 44

Materialized Views

Materialized Views. Objectives. After completing this lesson, you should be able to do the following: Describe how summaries can be used to improve performance Differentiate materialized view types Explain materialized view integrity Create a materialized view

kelsey-miller
Download Presentation

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

  2. Objectives • After completing this lesson, you should be able to do the following: • Describe how summaries can be used to improve performance • Differentiate materialized view types • Explain materialized view integrity • Create a materialized view • List globalization implications for materialized views

  3. The Need for Summary Management • How can you improve query response time? • Use indexes. • Partition your data. • Implement parallel execution. • What about precomputing query results? • Create summaries: Materialized views • Automatically rewrite SQL applications. • Automatically refresh data.

  4. Using Summaries to Improve Performance • Special types of aggregate views • Improve query execution time by precalculating expensive joins and aggregation operations before execution and storing results in a database table • Created using a schema object called a materialized view

  5. Using Summaries • Original query by user: • DBA creates summary table: • New query by user using summary table: SELECT c.cust_id, SUM(amount_sold) FROM sales s, customers c WHERE s.cust_id = c.cust_id GROUP BY c.cust_id; CREATE TABLE cust_sales_sum AS SELECT c.cust_id, SUM(amount_sold) AS amount FROM sales s, customers c WHERE s.cust_id = c.cust_id GROUP BY c.cust_id; SELECT * FROM cust_sales_sum;

  6. Using Materialized Views forSummary Management • DBA creates materialized view: • User issues original query: • Query is rewritten by the Oracle server: CREATE MATERIALIZED VIEW cust_sales_mv ENABLE QUERY REWRITE AS SELECT c.cust_id, SUM(amount_sold) FROM sales s, customers c WHERE s.cust_id = c.cust_id GROUP BY c.cust_id; SELECT c.cust_id, SUM(amount_sold) FROM sales s, customers c WHERE s.cust_id = c.cust_id GROUP BY c.cust_id; SELECT * FROM cust_sales_mv;

  7. Using Materialized Views for Summary Management 1 DBA creates materialized view. End user queries tables and views. 2 3 Oracle server rewrites SQL query to use materialized view.

  8. Summary Management Components • Mechanisms to define materialized views and dimensions • Refresh mechanism to ensure that materialized views contain the latest data • Query rewrite capability to transparently rewrite a query to use a materialized view • SQL Access Advisor: Recommends materialized views and indexes to be created • DBMS_ADVISOR.TUNE_MVIEW procedure: Shows you how to make your materialized view fast refreshable and use general query rewrite

  9. Using Summary Management 1. Use the SQL Access Advisor to determine how you will use materialized views. 2. Create materialized views and design how queries will be rewritten. 3. Use DBMS_ADVISOR.TUNE_MVIEW to obtain an optimized materialized view as necessary.

  10. How Many Materialized Views? • Query rewrite chooses which materialized view to use. • One materialized view per query: • Ideal for query performance • Consumes too much disk space • Not recommended • One materialized view for multiple queries: • One materialized view can be used to satisfy multiple queries • Less disk space needed • Less time needed to maintain materialized views

  11. One Materialized View for Multiple Queries CREATE MATERIALIZED VIEW cust_sales_mv2 ENABLE QUERY REWRITE AS SELECT cust_last_name, channel_id, SUM(amount_sold) FROM sales s, customers c WHERE s.cust_id = c.cust_id GROUP BY c.cust_last_name, s.channel_id; SELECT * FROM cust_sales_mv2; Query rewrite SELECT cust_last_name, channel_id, SUM(amount_sold) FROM sales s, customers c WHERE s.cust_id = c.cust_id GROUP BY c.cust_last_name, s.channel_id;

  12. One Materialized View for Multiple Queries CREATE MATERIALIZED VIEW cust_sales_mv2 ENABLE QUERY REWRITE AS SELECT cust_last_name, channel_id, SUM(amount_sold) FROM sales s, customers c WHERE s.cust_id = c.cust_id GROUP BY c.cust_last_name, s.channel_id; SELECT cust_last_name, SUM(amount_sold) FROM sales s, customers c WHERE s.cust_id = c.cust_id GROUP BY cust_last_name; SELECT cust_last_name, SUM(amount) FROM cust_sales_mv2 GROUP BY cust_id; Query rewrite

  13. One Materialized View for Multiple Queries CREATE MATERIALIZED VIEW cust_sales_mv2 ENABLE QUERY REWRITE AS SELECT cust_last_name, channel_id, SUM(amount_sold) FROM sales s, customers c WHERE s.cust_id = c.cust_id GROUP BY c.cust_last_name, s.channel_id; SELECT channel_id, SUM(amount_sold) FROM sales GROUP BY channel_id; SELECT channel_id, SUM(amount) FROM cust_sales_mv2 GROUP BY channel_id; Query rewrite

  14. Determining Which Materialized View to Create • One materialized view can be used to satisfy multiple queries. • Multiple materialized views can satisfy the same query. • A balance between performance and space usage must be found. • Which materialized view should you create? • Analyze your workload. • Use the SQL Access Advisor. • Use DBMS_MVIEW.EXPLAIN_REWRITE to see why a materialized view is used or ignored.

  15. Types of Materialized Views • Materialized views with aggregates: • Materialized views containing only joins: CREATE MATERIALIZED VIEW cust_sales_mv AS ENABLE QUERY REWRITE AS SELECT c.cust_id, s.channel_id, SUM(amount_sold) FROM sales s, customers c WHERE s.cust_id = c.cust_id GROUP BY c.cust_id, s.channel_id; CREATE MATERIALIZED VIEW sales_products_mv AS ENABLE QUERY REWRITE AS SELECT s.time_id, p.prod_name FROM sales s, products p WHERE s.prod_id = p.prod_id(+);

  16. The Need for Nested Materialized Views • Typical data warehouse need: Create aggregate materialized views using different grouping columns on a single join. • Maintaining the materialized views is time consuming because the underlying join is performed multiple times. SALES_TIME_PROD_MV SALES_PROD_TIME_MV SALES SALES TIMES TIMES PRODUCTS PRODUCTS Same tables joined Different grouping columns

  17. Using Nested Materialized Views • Definition is based on another materialized view. • Definition can also reference normal tables. SALES_TIME_PROD_MV SALES_PROD_TIME_MV SALES_PROD_TIME_JOIN Tables are joined one time. SALES TIMES PRODUCTS

  18. Nested Materialized Views: Restrictions • A nested materialized view cannot be a parent and a grandparent as shown: SALES_TIME_PROD_SUM Grandparent of PRODUCTS Parent of PRODUCTS SALES_TIMES_JOIN TIMES PRODUCTS SALES

  19. Materialized View: Example • CREATE MATERIALIZED VIEW cust_sales_mv • PCTFREE 0 TABLESPACE example • STORAGE (INITIAL 1M NEXT 1M PCTINCREASE 0) • BUILD DEFERRED • REFRESH COMPLETE • ENABLE QUERY REWRITE • AS SELECT c.cust_id, s.channel_id, • SUM(amount_sold) • FROM sales s, customers c • WHERE s.cust_id = c.cust_id • GROUP BY c.cust_id, s.channel_id • ORDER BY c.cust_id, s.channel_id; Name Storage options When to build it How to refresh the data Use this for query rewrite Detailquery Detail tables MV keys

  20. Materialized Views Storage • When a materialized view is created, the following objects are created: • A container table to store the materialized view rows • The materialized view itself • One index for materialized views with aggregates only • The OBJECT_TYPE column of DBA_OBJECTS contains MATERIALIZEDVIEW for the object. • The container table has the same name as the materialized view object. • A container table can be prebuilt.

  21. Estimating Materialized View Size Statement ID for EXPLAIN PLAN > VARIABLE num_rows NUMBER; > VARIABLE mv_size NUMBER; EXEC DBMS_MVIEW.ESTIMATE_MVIEW_SIZE( - 'simple_store', - 'SELECT c.cust_state_province, ' ||- ' SUM(amount_sold) ' ||- 'FROM sales s, customers c ' ||- 'WHERE s.cust_id= c.cust_id ' ||- 'GROUP BY c.cust_state_province', - :num_rows, :mv_size); Estimated storage in bytes Estimated rows

  22. Specifying Build Methods • Two build methods are available when creating the materialized view: • BUILD DEFERRED: Created but not populated • BUILD IMMEDIATE: Created and populated • The BUILD_MODE column in DBA_MVIEWS contains the method used.

  23. Specifying Refresh Options • Specify how the materialized view should be refreshed from the detail tables: • COMPLETE • FAST • FORCE • NEVER • The REFRESH_METHOD column in DBA_MVIEWS contains the option value.

  24. Specifying Refresh Execution Modes • Two refresh execution modes: • ON DEMAND: Manual • ON COMMIT: Refresh done at transaction commit; only possible for fast-refreshable materialized views. In case of failure, subsequent refreshes are manual. • Schedule: At regular intervals • The REFRESH_MODE column in DBA_MVIEWS contains the refresh execution mode value.

  25. Using Column Aliases in Materialized Views SELECT s.time_id, c.time_id FROM sales s, products p, costs c WHERE s.prod_id = p.prod_id AND c.prod_id = p.prod_id AND p.prod_name IN (SELECT prod_name FROM products); Query rewrite CREATE MATERIALIZED VIEW sales_mv ENABLE QUERY REWRITE AS SELECT s.time_id sales_tid, c.time_id costs_tid FROM sales s, products p, costs c WHERE s.prod_id = p.prod_id AND c.prod_id = p.prod_id AND p.prod_name IN (SELECT prod_name FROM products);

  26. Using Materialized View Column Alias Lists SELECT s.time_id, c.time_id FROM sales s, products p, costs c WHERE s.prod_id = p.prod_id AND c.prod_id = p.prod_id AND p.prod_name IN (SELECT prod_name FROM products); Query rewrite CREATE MATERIALIZED VIEW sales_mv (sales_tid, costs_tid) ENABLE QUERY REWRITE AS SELECT s.time_id, c.time_id FROM sales s, products p, costs c WHERE s.prod_id = p.prod_id AND c.prod_id = p.prod_id AND p.prod_name IN (SELECT prod_name FROM products);

  27. Registering User-Defined Materialized View Tables • Register existing materialized view tables with ON PREBUILT TABLE. • The registered materialized view can be used for query rewrites. • It can be maintained by refresh methods. • Requirements: • Table and materialized view have the same name. • Column aliases in detail query must correspond. • Column data types must match; can use the WITH REDUCEDPRECISION clause. • Table columns not referenced in the defining query are unmanaged columns. • Table remains after the materialized view is dropped.

  28. Partitioning and Materialized Views • Partitioning the fact tables: • Improves the opportunity of fast refreshing the materialized view • May enable Partition Change Tracking (PCT) refresh on the materialized view • Partitioning the materialized view: • Partition pruning can be used for query rewrite.

  29. Partitioned Materialized View: Example • CREATE MATERIALIZED VIEW part_sales_mv • PARALLEL PARTITION BY LIST (gid) • (PARTITION g1 VALUES (0), • PARTITION g2 VALUES (1), • PARTITION g3 VALUES (3)) • BUILD IMMEDIATE REFRESH COMPLETE • ENABLE QUERY REWRITE AS • SELECT prod_id,cust_id,time_id, • GROUPING_ID(prod_id,cust_id,time_id) AS gid, • sum(amount_sold) AS sum_sales • FROM sales • GROUP BY GROUPING SETS ((prod_id,cust_id,time_id), • (prod_id,cust_id),(prod_id));

  30. Using Enterprise Manager toCreate Materialized Views • cr_mv_gen.gif

  31. Privileges Required toCreate Materialized Views • Must be granted directly, not through roles • To create a materialized view in your schema: • CREATEMATERIALIZEDVIEW, and • CREATE[ANY]TABLE, and • SELECT privilege on each detail table not owned • To create a materialized view in another schema: • CREATEANYMATERIALIZEDVIEW, and • Materialized view owner must have • CREATE[ANY]TABLE, and • SELECT privilege on each detail table not owned

  32. Additional Privileges Required toCreate Materialized Views • To create a materialized view refreshed at commit time: • ONCOMMITREFRESH object privilege on each detail table not owned, or • ONCOMMITREFRESH system privilege • To enable query rewrite: • Detail table owner must have QUERYREWRITE system privilege • If you do not own detail tables: GLOBALQUERYREWRITE system privilege or QUERYREWRITE on each detail table not owned • SELECTWITHGRANTOPTION if materialized view is defined on a prebuilt table

  33. WHERE varchar_col = TO_DATE('01-FEB-02') • TO_DATE('01-FEB-2002', 'DD-MON-YYYY', 'NLS_DATE_LANGUAGE = AMERICAN') Globalization and Materialized Views • Materialized views use the settings in effect during materialized view creation. • Always specify globalization parameters to ensure that correct results are returned.

  34. NLS_CALENDAR NLS_NUMERIC_CHARACTERS NLS_COMP NLS_SORT NLS_CURRENCY NLS_TERRITORY NLS_DATE_FORMAT NLS_TIME_FORMAT NLS_DATE_LANGUAGE NLS_TIME_TZ_FORMAT NLS_DUAL_CURRENCY NLS_TIMESTAMP_FORMAT NLS_ISO_CURRENCY NLS_TIMESTAMP_TZ_FORMAT NLS_LANGUAGE Globalization Parameters Significant for Materialized Views

  35. Adding Comments to Materialized Views • Adding a comment for an existing materialized view: • Viewing comments: COMMENT ON MATERIALIZED VIEW cust_sales_mv IS 'sales materialized view'; • SELECT mview_name, comments • FROM user_mview_comments • WHERE mview_name = 'CUST_SALES_MV';

  36. Altering Materialized Views • Changing the refresh option and refresh mode: • Recompiling the materialized view: • Enabling or disabling its use for query rewrite: • ALTER MATERIALIZED VIEW cust_sales_mv • REFRESH FAST ON COMMIT; • ALTER MATERIALIZED VIEW cust_sales_mv COMPILE; • ALTER MATERIALIZED VIEW cust_sales_mv • DISABLE QUERY REWRITE;

  37. Altering Materialized Views • Allocating an extent: • Modifying the logging attribute: ALTER MATERIALIZED VIEW cust_sales_mv ALLOCATE EXTENT; • ALTER MATERIALIZED VIEW cust_sales_mv NOLOGGING;

  38. Maintaining Partitions of a Materialized View ALTER MATERIALIZED VIEW sales_mv TRUNCATE PARTITION year_1995; ALTER MATERIALIZED VIEW sales_mvDROP PARTITION year_1994; ALTER MATERIALIZED VIEW fact_mvEXCHANGE PARTITION year_2001 WITH TABLE sales_2001;

  39. Dropping Materialized Views • You must be the owner or you must have the DROP ANY MATERIALIZED VIEW system privilege. • You must have privileges to drop underlying objects. • Drop a materialized view using the DROP MATERIALIZED VIEW command: • If the materialized view was created on a prebuilt container table, the table is retained. • DROP MATERIALIZED VIEW cust_sales_mv;

  40. Viewing Staleness Information • Materialized view may diverge from the detail tables. • Query the STALENESS column in DBA_MVIEWS: • FRESH • STALE • UNUSABLE • UNKNOWN • UNDEFINED

  41. Materialized View Integrity • QUERY_REWRITE_INTEGRITY: Initialization parameter controls materialized view integrity. • Parameter values: • ENFORCED • TRUSTED • STALE_TOLERATED

  42. Invalidating Materialized Views • Automatic invalidation during dependency changes • Automatic revalidation during: • Refresh • Query rewrite • STATUS column in DBA_OBJECTS: • INVALID • VALID • COMPILE_STATE in DBA_MVIEWS: • VALID • NEEDS_COMPILE • Manual revalidation with: ALTER MATERIALIZED VIEW COMPILE

  43. Summary • In this lesson, you should have learned how to: • Use summaries in a data warehouse environment • Differentiate types of materialized views • Create a materialized view • Use the QUERY_REWRITE_INTEGRITY parameter

  44. Practice 9: Overview • This practice covers the following topics: • Creating materialized views on prebuilt tables • Estimating the number of rows of a potential materialized view • Creating new materialized views • Viewing objects created when materialized views are created

More Related