Materialized Views Presenter: Robert Webb Email: rwebb@RemoteControlDBA

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