Transcript No Slide Title

Materialized
Views
Presenter: Robert Webb
Email: [email protected]
Telephone: (888) 235-8916
Web: www.RemoteControlDBA.com
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.
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.
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.
Basic Terms (cont)

Fact Table – Also known as a Detail Table.
– Generally Very large.
– Records the Business Transactions.
– Examples: Sales and Expenses.
Creating a Materialized View
Init Parameters
 Privileges
 Statistics
 Materialized View Logs
(if using Fast Refresh)
 Materialized View Definition
 Statistics on Materialized View

Init Parameters

COMPATIBLE = 8.1.0 (or higher)

OPTIMIZER_MODE =
Choose, All_Rows, or First_Rows

QUERY_REWRITE_ENABLED = TRUE
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.
Statistics

ANALYZE TABLES

DBMS_STATS
Materialized View Log

Record changes to Base Tables.

Required for Incremental (FAST) Refresh.

Syntax;
CREATE MATERIALIZED VIEW LOG
SALES_LOG
WITH ROWID
(LOCATION, TIME, DOLLAR_SALES)
INCLUDING NEW VALUES;
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 ….
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 ….
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.

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 ….
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

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 ….
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.
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 ….
Query Rewrite

Alerts the Optimizer that this Materialized
View is eligible for Query Rewrite.

May be Enabled and Disabled.
Types of Materialized Views

Join only.

Single table aggregate.

Joins and aggregates.
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.
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.
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).
Query Rewrite

Query Rewrite is Transparent allowing
Materialized Views to be added or dropped
at any time without invalidating SQL or
Applications.
Query Rewrite
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.
Enabling Query Rewrite

Globally with Init Parameter
QUERY_REWRITE_ENABLED = TRUE

For Individual Views
ALTER MATERIALIZED VIEW example_mv
ENABLE QUERY REWRITE
Disabling Query Rewrite

Globally with Init Parameter
QUERY_REWRITE_ENABLED = FALSE

For Individual Views
ALTER MATERIALIZED VIEW example_mv
DISABLE QUERY REWRITE
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.
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.
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.
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.
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.
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.
Review – Creating Materialized Views
Init Parameters
 Privileges
 Statistics
 Materialized View Logs
(if using Fast Refresh)
 Materialized View Definition
 Statistics on Materialized View
