l What is a Materialized View?
– Advantages and Disadvantages
l How Materialized Views Work
– Parameter Settings, Privileges, Query Rewrite
l Creating Materialized Views
– Syntax, Refresh Modes/Options, Build Methods
– Examples
l Dimensions
– What are they?
– Examples
What is Materialized View?
l A database object that stores the results of a query
– Marries the query rewrite features found in Oracle Discoverer with the data refresh capabilities of snapshots
l Features/Capabilities
– Can be partitioned and indexed
– Can be queried directly
– Can have DML applied against it
– Several refresh options are available
– Best in read-intensive environments
Advantages and Disadvantages
l Advantages
– Useful for summarizing, pre-computing, replicating and distributing data
– Faster access for expensive and complex joins
– Transparent to end-users
l MVs can be added/dropped without invalidating coded SQL
l Disadvantages
– Performance costs of maintaining the views
– Storage costs of maintaining the views
Database Parameter Settings
l init.ora parameter
– COMPATIBLE=8.1.0 (or above)
l System or session settings
– query_rewrite_enabled={true|false}
– query_rewrite_integrity= {enforced|trusted|stale_tolerated}
– Can be set for a session using
– alter session set query_rewrite_enabled=true;
– alter session set query_rewrite_integrity=enforced;
– Privileges which must be granted to users directly
– QUERY_REWRITE - for MV using objects in own schema
– GLOBAL_QUERY_REWRITE - for objects in other schemas
Query Rewrite Details
l query_rewrite_integrity Settings:
– enforced – rewrites based on Oracle enforced constraints
l Primary key, foreign keys
– trusted – rewrites based on Oracle enforced constraints and known, but not enforced, data relationships
l Primary key, foreign keys
l Data dictionary information
l Dimensions
– stale_tolerated – queries rewritten even if Oracle knows the mv’s data is out-of-sync with the detail data
l Data dictionary information
l Query Rewrite Methods
– Full Exact Text Match
l Friendlier/more flexible version of text matching
– Partial Text Match
l Compares text starting at FROM clause
l SELECT clause must be satisfied for rewrite to occur
– Data Sufficiency
l All required data must be present in the MV or retrievable through a join-back operation
– Join Compatibility
l All joined columns are present in the MV
– Grouping Compatibility
l Allows for matches in groupings at higher levels than those defined MV query
l Required if both query and MV contain a GROUP BY clause
– Aggregate Compatibility
l Allows for interesting rewrites of aggregations
l If SUM(x) and COUNT(x) are in MV, the MV may be used if the query specifies AVG(x)
l Syntax For Materialized Views
CREATE MATERIALIZED VIEW <name>
TABLESPACE <tbs name> {<storage parameters>}
<build option>
REFRESH <refresh option> <refresh mode>
[ENABLE|DISABLE] QUERY REWRITE
AS
SELECT <select clause>;
l The <build option> determines when MV is built
– BUILD IMMEDIATE: view is built at creation time
– BUILD DEFFERED: view is built at a later time
– ON PREBUILT TABLE: use an existing table as view source
l Must set QUERY_REWRITE_INTEGRITY to TRUSTED
Materialized View Refresh Options
l Refresh Options
– COMPLETE – totally refreshes the view
l Can be done at any time; can be time consuming
– FAST – incrementally applies data changes
l A materialized view log is required on each detail table
l Data changes are recorded in MV logs or direct loader logs
l Many other requirements must be met for fast refreshes
– FORCE – does a FAST refresh in favor of a COMPLETE
l The default refresh option
Materialized View Refresh Modes
l Refresh Modes
– ON COMMIT – refreshes occur whenever a commit is performed on one of the view’s underlying detail table(s)
l Available only with single table aggregate or join based views
l Keeps view data transactionally accurate
l Need to check alert log for view creation errors
– ON DEMAND – refreshes are initiated manually using one of the procedures in the DBMS_MVIEW package
l Can be used with all types of materialized views
l Manual Refresh Procedures
– DBMS_MVIEW.REFRESH(<mv_name>, <refresh_option>)
– DBMS_MVIEW.REFRESH_ALL_MVIEWS()
– START WITH [NEXT] <date> - refreshes start at a specified date/time and continue at regular intervals
Materialized View Example
CREATE MATERIALIZED VIEW items_summary_mv
ON PREBUILT TABLE
REFRESH FORCE AS
SELECT a.PRD_ID, a.SITE_ID, a.TYPE_CODE, a.CATEG_ID,
sum(a.GMS) GMS,
sum(a.NET_REV) NET_REV,
sum(a.BOLD_FEE) BOLD_FEE,
sum(a.BIN_PRICE) BIN_PRICE,
sum(a.GLRY_FEE) GLRY_FEE,
sum(a.QTY_SOLD) QTY_SOLD,
count(a.ITEM_ID) UNITS
FROM items a
GROUP BY a.PRD_ID, a.SITE_ID, a.TYPE_CODE, a.CATEG_ID;
ANALYZE TABLE item_summary_mv COMPUTE STATISTICS;
Query to test impact of materialized view
select categ_id, site_id,
sum(net_rev),
sum(bold_fee),
count(item_id)
from items
where prd_id in ('2000M05','2000M06','2001M07','2001M08')
and site_id in (0,1)
and categ_id in (2,4,6,8,1,22)
group by categ_id, site_id
save mv_example.sql
SQL> ALTER SESSION SET QUERY_REWRITE_INTEGRITY=TRUSTED;
SQL> ALTER SESSION SET QUERY_REWRITE_ENABLED=FALSE;
SQL> @mv_example.sql
CATEG_ID SITE_ID SUM(NET_REV) SUM(BOLD_FEE) COUNT(ITEM_ID)
-------- ------- ------------ ------------- --------------
1 0 -2.35 0 1
22 0 -42120.87 -306 28085
Elapsed: 01:32:17.93
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=HINT: FIRST_ROWS (Cost=360829 Card=6 Bytes=120)
1 0 SORT (GROUP BY) (Cost=360829 Card=6 Bytes=120)
2 1 PARTITION RANGE (INLIST
3 2 TABLE ACCESS (FULL) OF ‘ITEMS' (Cost=360077
Card=375154 Bytes=7503080)
SQL> ALTER SESSION SET QUERY_REWRITE_ENABLED=TRUE;
SQL> @mv_example.sql
CATEG_ID SITE_ID SUM(NET_REV) SUM(BOLD_FEE) COUNT(ITEM_ID)
-------- ------- ------------ ------------- --------------
1 0 -2.35 0 1
22 0 -42120.87 -306 28085
Elapsed: 00:01:40.47
Execution Plan
----------------------------------------------------------------------------------------------
0 SELECT STATEMENT Optimizer=HINT: FIRST_ROWS (Cost=3749 Card=12 Bytes=276)
1 0 SORT (GROUP BY) (Cost=3749 Card=12 Bytes=276)
2 1 PARTITION RANGE (INLIST)
3 2 TABLE ACCESS (FULL) OF ‘ITEMS_SUMMARY_MV'
(Cost=3723 Card=7331 Bytes=168613)
Example of FAST REFRESH METERIAZED VIEW
CREATE MATERIALIZED VIEW LOG ON ITEMS
TABLESPACE MV_LOGS STORAGE(INITIAL 10M NEXT 10M) WITH ROWID;
CREATE MATERIALIZED VIEW LOG ON CUSTOMERS
TABLESPACE MV_LOGS STORAGE(INITIAL 1M NEXT 1M) WITH ROWID;
CREATE MATERIALIZED VIEW cust_activity
BUILD IMMEDIATE
REFRESH FAST ON COMMIT
AS
SELECT u.ROWID cust_rowid, l.ROWID item_rowid,
u.cust_id, u.custname, u.email,
l.categ_id, l.site_id, sum(gms), sum(net_rev_fee)
FROM customers u, items l
WHERE u.cust_id = l.seller_id
GROUP BY u.cust_id, u.custname, u.email, l.categ_id, l.site_id;
Getting Information About an MV
Getting information about the key columns of a materialized view:
SELECT POSITION_IN_SELECT POSITION,
CONTAINER_COLUMN COLUMN,
DETAILOBJ_OWNER OWNER,
DETAILOBJ_NAME SOURCE,
DETAILOBJ_ALIAS ALIAS,
DETAILOBJ_TYPE TYPE,
DETAILOBJ_COLUMN SRC_COLUMN
FROM USER_MVIEW_KEYS
WHERE MVIEW_NAME=‘ITEMS_SUMMARY_MV’;
POS COLUMN OWNER SOURCE ALIAS TYPE SRC_COLUMN
--- ---------- ----- -------- ----- ------ -----------
1 PRD_ID TAZ ITEMS A TABLE PRD_ID
2 SITE_ID TAZ ITEMS A TABLE SITE_ID
3 TYPE_CODE TAZ ITEMS A TABLE TYPE_CODE
4 CATEG_ID TAZ ITEMS A TABLE CATEG_ID
Getting information about the aggregate columns of a materialized view:
SELECT POSITION_IN_SELECT POSITION,
CONTAINER_COLUMN COLUMN,
AGG_FUNCTION
FROM USER_MVIEW_AGGREGATES
WHERE MVIEW_NAME=‘ITEMS_SUMMARY_MV’;
POSITION COLUMN AGG_FUNCTION
-------- ----------------- ------------
6 GMS SUM
7 NET_REV SUM
: : :
11 QTY_SOLD SUM
12 UNITS COUNT
Dimensions
l A way of describing complex data relationships
– Used to perform query rewrites, but not required
– Defines hierarchical relationships between pairs of columns
l Hierarchies can have multiple levels
l Each child in the hierarchy has one and only one parent
l Each level key can identify one or more attribute
l Child join keys must be NOT NULL
l Dimensions should be validated using the DBMS_OLAP.VALIDATE_DIMENSION package
– Bad row ROWIDs stored in table: mview$_exceptions
Syntax For Creating A Dimension
CREATE DIMENSION <dimension name>
LEVEL [<level> IS <level_table.level_column>
<level> IS <level_table.level_column>…]
HIERARCHY <hierarchy_name>
( <child_level> CHILD OF <parent_level>
<child_level> CHILD OF <parent_level>…]
ATTRIBUTE <level> DETERMINES <dependent_column>
<level> DETERMINES <dependent_column>,…);
To validate a dimension:
exec dbms_olap.validate_dimension(<dim_name>,<owner>,FALSE,FALSE
Example of Creating A Dimension
CREATE DIMENSION time_dim
LEVEL CAL_DATE IS calendar.CAL_DATE
LEVEL PRD_ID IS calendar.PRD_ID
LEVEL QTR_ID IS calendar.QTR_ID
LEVEL YEAR_ID IS calendar.YEAR_ID
LEVEL WEEK_IN_YEAR_ID IS calendar.WEEK_IN_YEAR_ID
HIERARCHY calendar_rollup
(CAL_DATE CHILD OF
PRD_ID CHILD OF
QTR_ID CHILD OF YEAR_ID)
HIERARCHY week_rollup
(CAL_DATE CHILD OF
WEEK_IN_YEAR_ID CHILD OF YEAR_ID)
ATTRIBUTE PRD_ID DETERMINES PRD_DESC
ATTRIBUTE QTR_ID DETERMINES QTR_DESC;
Example of Validating A Dimension
SQL> exec dbms_olap.validate_dimension(‘time_dim’, USER, FALSE, FALSE);
PL/SQL procedure successfully completed.
SQL> select * from mview$_exceptions;
no rows selected.
-- Main cause of errors is a child level having multiple parents
-- If above query returns rows, the bad rows can be found as follows:
select * from calendar
where rowid in
(select bad_rowid from mview$_exceptions);
Example of Using Dimensions
-- Step 1 of 4
-- Create materialized view (join-aggregate type)
CREATE MATERIALIZED VIEW items_mv
BUILD IMMEDIATE
REFRESH ON DEMAND
ENABLE QUERY REWRITE
AS
SELECT l.slr_id ,
c.cal_date,
sum(l.gms) gms
FROM items l,
calendar c
WHERE
l.end_date=c.cal_date
GROUP BY
l.slr_id, c.cal_date;
-- Step 1 of 4
-- Create materialized view (join-aggregate type)
CREATE MATERIALIZED VIEW items_mv
BUILD IMMEDIATE
REFRESH ON DEMAND
ENABLE QUERY REWRITE
AS
SELECT l.slr_id ,
c.cal_date,
sum(l.gms) gms
FROM items l,
calendar c
WHERE
l.end_date=c.cal_date
GROUP BY
l.slr_id, c.cal_date;
-- Step 3 of 4: Create time dimension (see slide #21 for SQL)
@cr_time_dim.sql
Dimension Created
-- Step 4 of 4: Rerun query based on “quarter” with time dimension
SQL> select c.qtr_id, sum(l.gms) gms
2 from items l, calendar c
3 where l.end_date=c.cal_date
4 group by l.slr_id, c.qtr_id;
Execution Plan
----------------------------------------------------------
SELECT STATEMENT Optimizer=CHOOSE (Cost=3703 Card=878824 Bytes=44820024)
SORT (GROUP BY) (Cost=3703 Card=878824 Bytes=44820024)
HASH JOIN (Cost=31 Card=878824 Bytes=44820024)
VIEW (Cost=25 Card=8017 Bytes=128272)
SORT (UNIQUE) (Cost=25 Card=8017 Bytes=128272)
TABLE ACCESS (FULL) OF ‘CALENDAR’ (Cost=2 Card=8017 Bytes=128272)
TABLE ACCESS (FULL) OF ‘ITEMS_MV’ (Cost=3 Card=10962 Bytes=383670)
Summary
l Materialized Views
– reduce system cpu/io resource requirements by pre-calculating and storing results of intensive queries
– allow for the automatic rewriting of intensive queries
– are transparent to the application
– have storage/maintenance requirements
– can understand complex data relationships
– can be refreshed on demand or on a schedule
Dimensions
Allow you to “tell” Oracle about complex data relationships which can be used to rewrite queries
Requirements for FAST REFRESH.
Requirement | Joins Only | Joins & Aggregates | Single Table Aggregates |
Must be based on detail tables only | X | X | X |
Must be based on a single table | X | ||
Each table can appear only once in the FROM list | X | X | X |
Cannot contain nonrepeating expressions (ROWNUM, SYSDATE, etc) | X | X | X |
Cannot contain references to RAW or LONG RAW | X | X | X |
Cannot contain the GROUP BY clause | X | ||
The SELECT list must include the ROWIDs of all the detail tables | X | ||
Expressions can be included in the GROUP BY and SELECT clause as long as they are the same in each | X | X | |
Aggregates are allowed but cannot be nested | X | X | |
If SELECT clause contains AVG, it must also contain COUNT | X | X | |
If SELECT clause contains SUM, it must also contain COUNT | X | ||
If SELECT clause contains VARIANCE, it must also contain COUNT and SUM | X | X | |
If SELECT clause contains STDDEV, it must also contain COUNT and SUM | X | ||
The join predicates of the WHERE clause can included AND but not OR | X | ||
The HAVING and CONNECT BY clauses are not allowed | X | X | X |
-queries, inline views, or set functions such as UNION are not allowed | X | X | X |
A WHERE clause is not allowed | X | ||
COUNT(*) must be present | X | ||
MIN and MAX are not allowed | X | ||
Unique constraints must exist on the join columns of the inner table, if an outer join is used | X | ||
A materialized view log must exist that contains all column referenced in the materialized view, and it must have been created with the LOG NEW VALUES clause | X | ||
A materialized view log containing ROWID must exist for each detail table | X | ||
Any non aggregate expressions in the SELECT and GROUP BY clauses must be non-modified columns Sub | X | ||
DML allowed on detailed tables | X | X | |
Direct path data load allowed | X | X | X |
References
l Using Oracle9i Materialized Views (Technet Oracle By Example)
– Oracle Expert-One-On-One – Thomas Kyte
l The Secrets of Materialized Views
– OLAP DB-Design with Dimensions
– http://www.akadia.com/services/ora_olap_dimensions.html
– The Secrets of Dimensions
No comments:
Post a Comment