1 / 35

Materialized Views Presenter: Robert Webb Email: rwebb@RemoteControlDBA

Materialized Views Presenter: Robert Webb Email: rwebb@RemoteControlDBA.com Telephone: (888) 235-8916 Web: www.RemoteControlDBA.com. Materialized Views.

aaron-roberts
Télécharger la présentation

Materialized Views Presenter: Robert Webb Email: rwebb@RemoteControlDBA

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 Presenter: Robert Webb Email: rwebb@RemoteControlDBA.com Telephone: (888) 235-8916 Web:www.RemoteControlDBA.com

  2. Materialized Views • Special kind of View to improve query execution times by precalculating Join and/or Aggregation operations prior to execution and storing the results in a database Table.

  3. Similar to Indexes • Designed to increase query Execution Performance. • Transparent to SQL Applications allowing DBA’s to create and drop Materialized Views without affecting the validity of Applications. • Consume Storage Space. • Can be Partitioned.

  4. Basic Terms • Dimension Table – Also known as a lookup table. • Generally small. • Describes the entities of a business, often in a categorical or hierarchical way. • Examples: Location, Department, or Products.

  5. Basic Terms (cont) • Fact Table – Also known as a Detail Table. • Generally Very large. • Records the Business Transactions. • Examples: Sales and Expenses.

  6. Creating a MaterializedView • Init Parameters • Privileges • Statistics • Materialized View Logs (if using Fast Refresh) • Materialized View Definition • Statistics on Materialized View

  7. Init Parameters • COMPATIBLE = 8.1.0 (or higher) • OPTIMIZER_MODE = Choose, All_Rows, or First_Rows • QUERY_REWRITE_ENABLED = TRUE

  8. Privileges • GRANT QUERY REWRITE TO SCOTT; • If all base tables are owned by Scott. • GRANT GLOBAL QUERY REWRITE TO SCOTT; • If some base tables are not owned by Scott. • Note: All Privileges, including SELECT privilege on base tables, must be granted explicitly – not inherited from a ROLE.

  9. Statistics • ANALYZE TABLES • DBMS_STATS

  10. Materialized View Log • Record changes to Base Tables. • Required for Incremental (FAST) Refresh. • Syntax;CREATE MATERIALIZED VIEW LOGSALES_LOG WITH ROWID(LOCATION, TIME, DOLLAR_SALES)INCLUDING NEW VALUES;

  11. Materialized View DDL Create Materialized View Example_mv Pctfree 0 tablespace mviews storage (initial 128K next 128K pctincrease 0) Parallel Build ImmediateRefresh Fast On Commit Enable Query Rewrite As Select ….

  12. Materialized View DDL Create Materialized View Example_mv Pctfree 0 tablespace mviews storage (initial 128K next 128K pctincrease 0) Parallel BUILD IMMEDIATE Refresh Fast On Commit Enable Query Rewrite As Select ….

  13. Build Options • Build Immediate – Populates the Materialized View as it is Created. • Build Deferred – Create the Materialized View but does Not populate it with data.

  14. Materialized View DDL Create Materialized View Example_mv Pctfree 0 tablespace mviews storage (initial 128K next 128K pctincrease 0) Parallel Build Immediate REFRESH FAST On Commit Enable Query Rewrite As Select ….

  15. Refresh Options • Complete – Truncates existing data and recalculates the defining query. • Fast – Incrementally adds new data. The data is obtained from the Materialized View Log or Direct Path Log. • Force – Performs Fast Refresh if possible, otherwise a Complete Refresh is performed. • Never – Does not Refresh

  16. Materialized View DDL Create Materialized View Example_mv Pctfree 0 tablespace mviews storage (initial 128K next 128K pctincrease 0) Parallel Build Immediate Refresh Fast ON COMMIT Enable Query Rewrite As Select ….

  17. Refresh Mode • ON COMMIT – Materialized View is refreshed automatically when transaction commits a change to an underlying table. • Restricted to Single Table Aggregate and Join only Materialized Views. • ON DEMAND – User manually executes one of the refresh options available in the DBMS_MVIEW package. • DBMS_MVIEW.Refresh_All_Mviews.

  18. Materialized View DDL Create Materialized View Example_mv Pctfree 0 tablespace mviews storage (initial 128K next 128K pctincrease 0) Parallel Build Immediate Refresh Fast On Commit Enable Query Rewrite As Select ….

  19. Query Rewrite • Alerts the Optimizer that this Materialized View is eligible for Query Rewrite. • May be Enabled and Disabled.

  20. Types of Materialized Views • Join only. • Single table aggregate. • Joins and aggregates.

  21. Join Only • Allows expensive Joins to precalculated. • Fast Refresh Option available • On Commit Refresh Mode available. • Materialized View Log required for each table. • RowID’s must be in the Select list of the Materialized View query. • For faster refresh create an index on the RowID column of the Materialized View.

  22. Single Table Aggregate • Precalculate expensive Aggregates (Sum, Avg, Stddev, Count) • Fast Refresh Option available. • Requires Materialized View Log with the INCLUDING NEW VALUES clause. • On Commit Refresh Mode available. • Count(*) is always required in Select clause.

  23. Join and Aggregate • Precalculate expensive Joins and Aggregates. • Restricted Refresh options. • Fast Refresh Mode available • Only after Direct Path Loads. • Not available after DML (On Commit).

  24. Query Rewrite • Query Rewrite is Transparent allowing Materialized Views to be added or dropped at any time without invalidating SQL or Applications.

  25. Query Rewrite

  26. When does Rewrite Occur • Query Rewrite must be enabled for the session. • Query Rewrite must be enabled for the Materialized View. • Rewrite Integrity Level allows use of Materialized View • All or Part of the results should be available from the Materialized View.

  27. Enabling Query Rewrite • Globally with Init Parameter QUERY_REWRITE_ENABLED = TRUE • For Individual Views ALTER MATERIALIZED VIEW example_mv ENABLE QUERY REWRITE

  28. Disabling Query Rewrite • Globally with Init Parameter QUERY_REWRITE_ENABLED = FALSE • For Individual ViewsALTER MATERIALIZED VIEW example_mv DISABLE QUERY REWRITE

  29. Rewrite Integrity • Enforced • Default and Safest Integrity Level • Must contain fresh data • Relationships must be based on Enforced constraints. • Trusted • Data is Trusted to be accurate • Relationships declared in Dimensions & Rely constraints are Trusted. • Stale Tolerated • Views with Stale data are Tolerated. • Useful for Point in Time snapshots.

  30. Cost Based Rewrite • The Optimizer must be Cost Based to utilize Query Rewrite (Choose, First_Rows, All_Rows) • Statistics must be accurate for the underlying tables as well as the Materialized View.

  31. Did the Rewrite Occur • Query execution times will often be all that is needed. • Explain Plans will show clearly that the Materialized View is being accessed rather than the underlying tables.

  32. Summary Advisor • Materialized View Analysis and Advisory functions are available using the DBMS_OLAP Package. • Recommend New Materialized Views. • Report Actual Utilization of Materialized Views. • Estimate Size of proposed Materialized Views.

  33. Review – Materialized View • Special kind of View to improve query execution times by precalculating Join and/or Aggregation operations prior to execution and storing the results in a database Table.

  34. Review – Similar to Indexes • Designed to increase query Execution Performance. • Transparent to SQL Applications allowing DBA’s to create and drop Materialized Views without affecting the validity of Applications. • Consume Storage Space. • Can be Partitioned.

  35. Review – Creating Materialized Views • Init Parameters • Privileges • Statistics • Materialized View Logs (if using Fast Refresh) • Materialized View Definition • Statistics on Materialized View

More Related