materialized views presenter: robert webb email: rwebb@remotecontroldba
DESCRIPTION
Materialized Views Presenter: Robert Webb Email: [email protected] Telephone: (888) 235-8916 Web: www.RemoteControlDBA.com. Materialized Views. - PowerPoint PPT PresentationTRANSCRIPT
![Page 1: Materialized Views Presenter: Robert Webb Email: rwebb@RemoteControlDBA](https://reader031.vdocuments.us/reader031/viewer/2022032607/56813069550346895d9646ce/html5/thumbnails/1.jpg)
Materialized Views
Presenter: Robert Webb
Email: [email protected]: (888) 235-8916Web: www.RemoteControlDBA.com
![Page 2: Materialized Views Presenter: Robert Webb Email: rwebb@RemoteControlDBA](https://reader031.vdocuments.us/reader031/viewer/2022032607/56813069550346895d9646ce/html5/thumbnails/2.jpg)
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.
![Page 3: Materialized Views Presenter: Robert Webb Email: rwebb@RemoteControlDBA](https://reader031.vdocuments.us/reader031/viewer/2022032607/56813069550346895d9646ce/html5/thumbnails/3.jpg)
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.
![Page 4: Materialized Views Presenter: Robert Webb Email: rwebb@RemoteControlDBA](https://reader031.vdocuments.us/reader031/viewer/2022032607/56813069550346895d9646ce/html5/thumbnails/4.jpg)
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.
![Page 5: Materialized Views Presenter: Robert Webb Email: rwebb@RemoteControlDBA](https://reader031.vdocuments.us/reader031/viewer/2022032607/56813069550346895d9646ce/html5/thumbnails/5.jpg)
Basic Terms (cont)
Fact Table – Also known as a Detail Table.
– Generally Very large.
– Records the Business Transactions.
– Examples: Sales and Expenses.
![Page 6: Materialized Views Presenter: Robert Webb Email: rwebb@RemoteControlDBA](https://reader031.vdocuments.us/reader031/viewer/2022032607/56813069550346895d9646ce/html5/thumbnails/6.jpg)
Creating a Materialized View
Init ParametersPrivilegesStatisticsMaterialized View Logs
(if using Fast Refresh)Materialized View DefinitionStatistics on Materialized View
![Page 7: Materialized Views Presenter: Robert Webb Email: rwebb@RemoteControlDBA](https://reader031.vdocuments.us/reader031/viewer/2022032607/56813069550346895d9646ce/html5/thumbnails/7.jpg)
Init Parameters
COMPATIBLE = 8.1.0 (or higher)
OPTIMIZER_MODE = Choose, All_Rows, or First_Rows
QUERY_REWRITE_ENABLED = TRUE
![Page 8: Materialized Views Presenter: Robert Webb Email: rwebb@RemoteControlDBA](https://reader031.vdocuments.us/reader031/viewer/2022032607/56813069550346895d9646ce/html5/thumbnails/8.jpg)
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.
![Page 9: Materialized Views Presenter: Robert Webb Email: rwebb@RemoteControlDBA](https://reader031.vdocuments.us/reader031/viewer/2022032607/56813069550346895d9646ce/html5/thumbnails/9.jpg)
Statistics
ANALYZE TABLES
DBMS_STATS
![Page 10: Materialized Views Presenter: Robert Webb Email: rwebb@RemoteControlDBA](https://reader031.vdocuments.us/reader031/viewer/2022032607/56813069550346895d9646ce/html5/thumbnails/10.jpg)
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;
![Page 11: Materialized Views Presenter: Robert Webb Email: rwebb@RemoteControlDBA](https://reader031.vdocuments.us/reader031/viewer/2022032607/56813069550346895d9646ce/html5/thumbnails/11.jpg)
Materialized View DDL
Create Materialized View Example_mvPctfree 0 tablespace mviewsstorage (initial 128K next 128K pctincrease 0)ParallelBuild ImmediateRefresh Fast On CommitEnable Query Rewrite As Select ….
![Page 12: Materialized Views Presenter: Robert Webb Email: rwebb@RemoteControlDBA](https://reader031.vdocuments.us/reader031/viewer/2022032607/56813069550346895d9646ce/html5/thumbnails/12.jpg)
Materialized View DDL
Create Materialized View Example_mvPctfree 0 tablespace mviewsstorage (initial 128K next 128K pctincrease 0)ParallelBUILD IMMEDIATERefresh Fast On CommitEnable Query Rewrite As Select ….
![Page 13: Materialized Views Presenter: Robert Webb Email: rwebb@RemoteControlDBA](https://reader031.vdocuments.us/reader031/viewer/2022032607/56813069550346895d9646ce/html5/thumbnails/13.jpg)
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.
![Page 14: Materialized Views Presenter: Robert Webb Email: rwebb@RemoteControlDBA](https://reader031.vdocuments.us/reader031/viewer/2022032607/56813069550346895d9646ce/html5/thumbnails/14.jpg)
Materialized View DDL
Create Materialized View Example_mvPctfree 0 tablespace mviewsstorage (initial 128K next 128K pctincrease 0)ParallelBuild ImmediateREFRESH FAST On CommitEnable Query Rewrite As Select ….
![Page 15: Materialized Views Presenter: Robert Webb Email: rwebb@RemoteControlDBA](https://reader031.vdocuments.us/reader031/viewer/2022032607/56813069550346895d9646ce/html5/thumbnails/15.jpg)
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
![Page 16: Materialized Views Presenter: Robert Webb Email: rwebb@RemoteControlDBA](https://reader031.vdocuments.us/reader031/viewer/2022032607/56813069550346895d9646ce/html5/thumbnails/16.jpg)
Materialized View DDL
Create Materialized View Example_mvPctfree 0 tablespace mviewsstorage (initial 128K next 128K pctincrease 0)ParallelBuild ImmediateRefresh Fast ON COMMITEnable Query Rewrite As Select ….
![Page 17: Materialized Views Presenter: Robert Webb Email: rwebb@RemoteControlDBA](https://reader031.vdocuments.us/reader031/viewer/2022032607/56813069550346895d9646ce/html5/thumbnails/17.jpg)
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.
![Page 18: Materialized Views Presenter: Robert Webb Email: rwebb@RemoteControlDBA](https://reader031.vdocuments.us/reader031/viewer/2022032607/56813069550346895d9646ce/html5/thumbnails/18.jpg)
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 ….
![Page 19: Materialized Views Presenter: Robert Webb Email: rwebb@RemoteControlDBA](https://reader031.vdocuments.us/reader031/viewer/2022032607/56813069550346895d9646ce/html5/thumbnails/19.jpg)
Query Rewrite
Alerts the Optimizer that this Materialized View is eligible for Query Rewrite.
May be Enabled and Disabled.
![Page 20: Materialized Views Presenter: Robert Webb Email: rwebb@RemoteControlDBA](https://reader031.vdocuments.us/reader031/viewer/2022032607/56813069550346895d9646ce/html5/thumbnails/20.jpg)
Types of Materialized Views
Join only.
Single table aggregate.
Joins and aggregates.
![Page 21: Materialized Views Presenter: Robert Webb Email: rwebb@RemoteControlDBA](https://reader031.vdocuments.us/reader031/viewer/2022032607/56813069550346895d9646ce/html5/thumbnails/21.jpg)
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.
![Page 22: Materialized Views Presenter: Robert Webb Email: rwebb@RemoteControlDBA](https://reader031.vdocuments.us/reader031/viewer/2022032607/56813069550346895d9646ce/html5/thumbnails/22.jpg)
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.
![Page 23: Materialized Views Presenter: Robert Webb Email: rwebb@RemoteControlDBA](https://reader031.vdocuments.us/reader031/viewer/2022032607/56813069550346895d9646ce/html5/thumbnails/23.jpg)
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).
![Page 24: Materialized Views Presenter: Robert Webb Email: rwebb@RemoteControlDBA](https://reader031.vdocuments.us/reader031/viewer/2022032607/56813069550346895d9646ce/html5/thumbnails/24.jpg)
Query Rewrite
Query Rewrite is Transparent allowing Materialized Views to be added or dropped at any time without invalidating SQL or Applications.
![Page 25: Materialized Views Presenter: Robert Webb Email: rwebb@RemoteControlDBA](https://reader031.vdocuments.us/reader031/viewer/2022032607/56813069550346895d9646ce/html5/thumbnails/25.jpg)
Query Rewrite
![Page 26: Materialized Views Presenter: Robert Webb Email: rwebb@RemoteControlDBA](https://reader031.vdocuments.us/reader031/viewer/2022032607/56813069550346895d9646ce/html5/thumbnails/26.jpg)
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.
![Page 27: Materialized Views Presenter: Robert Webb Email: rwebb@RemoteControlDBA](https://reader031.vdocuments.us/reader031/viewer/2022032607/56813069550346895d9646ce/html5/thumbnails/27.jpg)
Enabling Query Rewrite
Globally with Init Parameter QUERY_REWRITE_ENABLED = TRUE
For Individual Views ALTER MATERIALIZED VIEW example_mv ENABLE QUERY REWRITE
![Page 28: Materialized Views Presenter: Robert Webb Email: rwebb@RemoteControlDBA](https://reader031.vdocuments.us/reader031/viewer/2022032607/56813069550346895d9646ce/html5/thumbnails/28.jpg)
Disabling Query Rewrite
Globally with Init Parameter QUERY_REWRITE_ENABLED = FALSE
For Individual ViewsALTER MATERIALIZED VIEW example_mv DISABLE QUERY REWRITE
![Page 29: Materialized Views Presenter: Robert Webb Email: rwebb@RemoteControlDBA](https://reader031.vdocuments.us/reader031/viewer/2022032607/56813069550346895d9646ce/html5/thumbnails/29.jpg)
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.
![Page 30: Materialized Views Presenter: Robert Webb Email: rwebb@RemoteControlDBA](https://reader031.vdocuments.us/reader031/viewer/2022032607/56813069550346895d9646ce/html5/thumbnails/30.jpg)
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.
![Page 31: Materialized Views Presenter: Robert Webb Email: rwebb@RemoteControlDBA](https://reader031.vdocuments.us/reader031/viewer/2022032607/56813069550346895d9646ce/html5/thumbnails/31.jpg)
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.
![Page 32: Materialized Views Presenter: Robert Webb Email: rwebb@RemoteControlDBA](https://reader031.vdocuments.us/reader031/viewer/2022032607/56813069550346895d9646ce/html5/thumbnails/32.jpg)
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.
![Page 33: Materialized Views Presenter: Robert Webb Email: rwebb@RemoteControlDBA](https://reader031.vdocuments.us/reader031/viewer/2022032607/56813069550346895d9646ce/html5/thumbnails/33.jpg)
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.
![Page 34: Materialized Views Presenter: Robert Webb Email: rwebb@RemoteControlDBA](https://reader031.vdocuments.us/reader031/viewer/2022032607/56813069550346895d9646ce/html5/thumbnails/34.jpg)
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.
![Page 35: Materialized Views Presenter: Robert Webb Email: rwebb@RemoteControlDBA](https://reader031.vdocuments.us/reader031/viewer/2022032607/56813069550346895d9646ce/html5/thumbnails/35.jpg)
Review – Creating Materialized Views
Init ParametersPrivilegesStatisticsMaterialized View Logs
(if using Fast Refresh)Materialized View DefinitionStatistics on Materialized View