Materialized Views

Willie Albino

May 15, 2003

Materialized Views Agenda

What is a Materialized View?

Advantages and Disadvantages

How Materialized Views Work

Parameter Settings, Privileges, Query Rewrite

Syntax, Refresh Modes/Options, Build Methods Examples What are they? Examples

Creating Materialized Views

Dimensions

Willie Albino

May 15, 2003

What is a Materialized View?

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

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

Willie Albino

May 15, 2003

Advantages and Disadvantages

Advantages

Useful for summarizing, pre-computing, replicating and distributing data Faster access for expensive and complex joins Transparent to end-users

MVs can be added/dropped without invalidating coded SQL

Disadvantages

Performance costs of maintaining the views Storage costs of maintaining the views

Willie Albino

May 15, 2003

Database Parameter Settings

init.ora parameter

COMPATIBLE=8.1.0 (or above) query_rewrite_enabled={true|false} query_rewrite_integrity= {enforced|trusted|stale_tolerated} alter session set query_rewrite_enabled=true; alter session set query_rewrite_integrity=enforced;

System or session settings

Can be set for a session using

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
Willie Albino May 15, 2003

Query Rewrite Details

query_rewrite_integrity Settings:

enforced rewrites based on Oracle enforced constraints

Primary key, foreign keys

trusted rewrites based on Oracle enforced constraints and known, but not enforced, data relationships

Primary key, foreign keys Data dictionary information Dimensions

stale_tolerated queries rewritten even if Oracle knows the mvs data is out-of-sync with the detail data

Data dictionary information

Willie Albino May 15, 2003

Query Rewrite Details (contd)

Query Rewrite Methods

Full Exact Text Match

Friendlier/more flexible version of text matching Compares text starting at FROM clause SELECT clause must be satisfied for rewrite to occur All required data must be present in the MV or retrievable through a join-back operation All joined columns are present in the MV

Partial Text Match

Data Sufficiency

Join Compatibility

Willie Albino

May 15, 2003

Query Rewrite Details (contd)

Grouping Compatibility

Allows for matches in groupings at higher levels than those defined MV query Required if both query and MV contain a GROUP BY clause Allows for interesting rewrites of aggregations

Aggregate Compatibility

If SUM(x) and COUNT(x) are in MV, the MV may be used if the query specifies AVG(x)

Willie Albino

May 15, 2003

Syntax For Materialized Views

CREATE MATERIALIZED VIEW <name> TABLESPACE <tbs name> <build option> REFRESH <refresh option> <refresh mode> [ENABLE|DISABLE] QUERY REWRITE AS SELECT <select clause>;
The

{<storage parameters>}

<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 Must set QUERY_REWRITE_INTEGRITY to TRUSTED
Willie Albino May 15, 2003

Materialized View Refresh Options

Refresh Options

COMPLETE totally refreshes the view

Can be done at any time; can be time consuming

FAST incrementally applies data changes

A materialized view log is required on each detail table Data changes are recorded in MV logs or direct loader logs Many other requirements must be met for fast refreshes

FORCE does a FAST refresh in favor of a COMPLETE

The default refresh option

10

Willie Albino

May 15, 2003

Materialized View Refresh Modes

Refresh Modes

ON COMMIT refreshes occur whenever a commit is performed on one of the views underlying detail table(s)

Available only with single table aggregate or join based views Keeps view data transactionally accurate 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

Can be used with all types of materialized views 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
Willie Albino May 15, 2003

11

Materialized View Example

CREATE MATERIALIZED VIEW items_summary_mv ON PREBUILT TABLE REFRESH FORCE SELECT AS GMS, NET_REV, BOLD_FEE, GLRY_FEE, QTY_SOLD, a.PRD_ID, a.SITE_ID, a.TYPE_CODE, a.CATEG_ID, sum(a.GMS) sum(a.NET_REV) sum(a.BOLD_FEE) sum(a.GLRY_FEE) sum(a.QTY_SOLD) FROM items a a.PRD_ID, a.SITE_ID, a.TYPE_CODE, a.CATEG_ID;

sum(a.BIN_PRICE) BIN_PRICE,

count(a.ITEM_ID) UNITS GROUP BY

ANALYZE TABLE item_summary_mv COMPUTE STATISTICS;

12

Willie Albino

May 15, 2003

Materialized View Example (contd)

-- 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

13

Willie Albino

May 15, 2003

Materialized View Example (contd)

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 22 0 0 -2.35 -42120.87 0 -306 1 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)

14

Willie Albino

May 15, 2003

Materialized View Example (contd)

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 22 0 0 -2.35 -42120.87 0 -306 1 28085

Elapsed: 00:01:40.47 Execution Plan

----------------------------------------------------------------------------------------------

0 1 2 3 0 1 2

SELECT STATEMENT Optimizer=HINT: FIRST_ROWS (Cost=3749 Card=12 Bytes=276) SORT (GROUP BY) (Cost=3749 Card=12 Bytes=276) PARTITION RANGE (INLIST) TABLE ACCESS (FULL) OF ITEMS_SUMMARY_MV' (Cost=3723 Card=7331 Bytes=168613)

15

Willie Albino

May 15, 2003

Example of FAST REFRESH MV

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;

16

Willie Albino

May 15, 2003

Getting Information About an MV

Getting information about the key columns of a materialized view:

SELECT POSITION_IN_SELECT CONTAINER_COLUMN DETAILOBJ_OWNER DETAILOBJ_NAME DETAILOBJ_ALIAS DETAILOBJ_TYPE DETAILOBJ_COLUMN FROM USER_MVIEW_KEYS WHERE MVIEW_NAME=ITEMS_SUMMARY_MV; POSITION, COLUMN, OWNER, SOURCE, ALIAS, TYPE, SRC_COLUMN

POS COLUMN
1 2 3 4 PRD_ID SITE_ID TYPE_CODE CATEG_ID

OWNER
TAZ TAZ TAZ TAZ

SOURCE
ITEMS ITEMS ITEMS ITEMS

ALIAS TYPE
A A A A TABLE TABLE TABLE TABLE

SRC_COLUMN
PRD_ID SITE_ID TYPE_CODE CATEG_ID

--- ---------- ----- -------- ----- ------ -----------

17

Willie Albino

May 15, 2003

Getting Information About an MV

Getting information about the aggregate columns of a materialized view:

SELECT POSITION_IN_SELECT CONTAINER_COLUMN AGG_FUNCTION FROM USER_MVIEW_AGGREGATES WHERE MVIEW_NAME=ITEMS_SUMMARY_MV; POSITION, COLUMN,

POSITION -------6 7 : 11 12

COLUMN ----------------GMS NET_REV : QTY_SOLD UNITS

AGG_FUNCTION -----------SUM SUM : SUM COUNT

18

Willie Albino

May 15, 2003

Dimensions

A way of describing complex data relationships

Used to perform query rewrites, but not required Defines hierarchical relationships between pairs of columns

Hierarchies can have multiple levels Each child in the hierarchy has one and only one parent Each level key can identify one or more attribute Child join keys must be NOT NULL

Dimensions should be validated using the DBMS_OLAP.VALIDATE_DIMENSION package Bad row ROWIDs stored in table: mview$_exceptions
Willie Albino May 15, 2003

19

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);

20

Willie Albino

May 15, 2003

Example of Creating A Dimension

CREATE DIMENSION time_dim LEVEL CAL_DATE IS calendar.CAL_DATE LEVEL PRD_ID LEVEL QTR_ID LEVEL YEAR_ID IS calendar.PRD_ID IS calendar.QTR_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;

21

Willie Albino

May 15, 2003

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);

22

Willie Albino

May 15, 2003

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;

23

Willie Albino

May 15, 2003

Example of Using Dimensions (contd)

-- Step 2 of 4: (not really required, for demonstration only) -- Execute query based on quarter, not date, without a time dimension -- Note that the detail tables are accessed SQL> 2 3 4 select c.qtr_id, sum(l.gms) gms from items l, calendar c where l.end_date=c.cal_date group by l.slr_id, c.qtr_id;

Execution Plan ---------------------------------------------------------SELECT STATEMENT Optimizer=CHOOSE (Cost=16174 Card=36258 Bytes=1160256) SORT (GROUP BY) (Cost=16174 Card=36258 Bytes=1160256) HASH JOIN (Cost=81 Card=5611339 Bytes=179562848) TABLE ACCESS (FULL) OF CALENDAR' (Cost=2 Card=8017 Bytes=128272) TABLE ACCESS (FULL) OF ITEMS' (Cost=76 Card=69993 Bytes=1119888)

24

Willie Albino

May 15, 2003

Example of Using Dimensions (contd)

-- 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> 2 3 4 select c.qtr_id, sum(l.gms) gms from items l, calendar c where l.end_date=c.cal_date 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)

25

Willie Albino

May 15, 2003

Summary

Materialized Views

reduce system cpu/io resource requirements by precalculating 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 allow you to tell Oracle about complex data relationships which can be used to rewrite queries
Willie Albino May 15, 2003

Dimensions

26

References

Using Oracle9i Materialized Views (Technet Oracle By Example)

http://technet.oracle.com/products/oracle9i/htdocs/9iober2/obe9ir2/obedwh/html/mv/mv.htm

Oracle Expert-One-On-One Thomas Kyte

The Secrets of Materialized Views

http://www.akadia.com/services/ora_materialized_views.html

OLAP DB-Design with Dimensions

http://www.akadia.com/services/ora_olap_dimensions.html

The Secrets of Dimensions

http://www.akadia.com/services/ora_dimensions.html
Willie Albino May 15, 2003

27

28

Willie Albino

May 15, 2003

Requirements for FAST REFRESH

Requirement Must be based on detail tables only Must be based on a single table Each table can appear only once in the FROM list Cannot contain nonrepeating expressions (ROWNUM, SYSDATE, etc) Cannot contain references to RAW or LONG RAW Cannot contain the GROUP BY clause The SELECT list must include the ROWIDs of all the detail tables Expressions can be included in the GROUP BY and SELECT clause as long as they are the same in each Aggregates are allowed but cannot be nested If SELECT clause contains AVG, it must also contain COUNT If SELECT clause contains SUM, it must also contain COUNT If SELECT clause contains VARIANCE, it must also contain COUNT and SUM If SELECT clause contains STDDEV, it must also contain COUNT and SUM The join predicates of the WHERE clause can included AND but not OR The HAVING and CONNECT BY clauses are not allowed Joins Only X X X X X X Joins & Single Table Aggregates Aggregates X X X X X X X X X

X X X X

X X X X X X

X X

29

Willie Albino

May 15, 2003

Rqmts For FAST REFRESH (contd)

Requirement Sub-queries, inline views, or set functions such as UNION are not allowed A WHERE clause is not allowed COUNT(*) must be present MIN and MAX are not allowed Unique constraints must exist on the join columns of the inner table, if an outer join is used 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 A materialized view log containing ROWID must exist for each detail table Any non aggregate expressions in the SELECT and GROUP BY clauses must be non-modified columns DML allowed on detailed tables Direct path data load allowed

Joins Only X

Joins & Single Table Aggregates Aggregates X X X X X

X X

X X X X X X

30

Willie Albino

May 15, 2003