Materialized views
Download
1 / 30

Materialized Views - PowerPoint PPT Presentation


  • 337 Views
  • Updated On :

Materialized Views. Materialized Views – Agenda. What is a Materialized View? Advantages and Disadvantages How Materialized Views Work Parameter Settings, Privileges, Query Rewrite Creating Materialized Views Syntax, Refresh Modes/Options, Build Methods Examples Dimensions What are they?

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
Download Presentation

PowerPoint Slideshow about 'Materialized Views' - issac


An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
Materialized views l.jpg

Materialized Views

Willie Albino May 15, 2003


Materialized views agenda l.jpg
Materialized Views – Agenda

  • What is a Materialized View?

    • Advantages and Disadvantages

  • How Materialized Views Work

    • Parameter Settings, Privileges, Query Rewrite

  • Creating Materialized Views

    • Syntax, Refresh Modes/Options, Build Methods

    • Examples

  • Dimensions

    • What are they?

    • Examples

Willie Albino May 15, 2003


What is a materialized view l.jpg
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 l.jpg
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 l.jpg
Database Parameter Settings

  • init.ora parameter

    • COMPATIBLE=8.1.0 (or above)

  • 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

Willie Albino May 15, 2003


Query rewrite details l.jpg
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 mv’s data is out-of-sync with the detail data

      • Data dictionary information

Willie Albino May 15, 2003


Query rewrite details cont d l.jpg
Query Rewrite Details (cont’d)

  • Query Rewrite Methods

    • Full Exact Text Match

      • Friendlier/more flexible version of text matching

    • Partial Text Match

      • Compares text starting at FROM clause

      • SELECT clause must be satisfied for rewrite to occur

    • Data Sufficiency

      • All required data must be present in the MV or retrievable through a join-back operation

    • Join Compatibility

      • All joined columns are present in the MV

Willie Albino May 15, 2003


Query rewrite details cont d8 l.jpg
Query Rewrite Details (cont’d)

  • 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

  • Aggregate Compatibility

    • Allows for interesting rewrites of aggregations

      • 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 l.jpg
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>;

  • 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

      • Must set QUERY_REWRITE_INTEGRITY to TRUSTED

Willie Albino May 15, 2003


Materialized view refresh options l.jpg
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

Willie Albino May 15, 2003


Materialized view refresh modes l.jpg
Materialized View Refresh Modes

  • Refresh Modes

    • ON COMMIT – refreshes occur whenever a commit is performed on one of the view’s 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


Materialized view example l.jpg
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;

Willie Albino May 15, 2003


Materialized view example cont d l.jpg
Materialized View Example (cont’d)

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

Willie Albino May 15, 2003


Materialized view example cont d14 l.jpg
Materialized View Example (cont’d)

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)

Willie Albino May 15, 2003


Materialized view example cont d15 l.jpg
Materialized View Example (cont’d)

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)

Willie Albino May 15, 2003


Example of fast refresh mv l.jpg
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;

Willie Albino May 15, 2003


Getting information about an mv l.jpg
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

Willie Albino May 15, 2003


Getting information about an mv18 l.jpg
Getting Information About an MV

  • 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

Willie Albino May 15, 2003


Dimensions l.jpg
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


Syntax for creating a dimension l.jpg
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);

Willie Albino May 15, 2003


Example of creating a dimension l.jpg
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;

Willie Albino May 15, 2003


Example of validating a dimension l.jpg
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);

Willie Albino May 15, 2003


Example of using dimensions l.jpg
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;

Willie Albino May 15, 2003


Example of using dimensions cont d l.jpg
Example of Using Dimensions (cont’d)

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

Willie Albino May 15, 2003


Example of using dimensions cont d25 l.jpg
Example of Using Dimensions (cont’d)

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

Willie Albino May 15, 2003


Summary l.jpg
Summary

  • 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

Willie Albino May 15, 2003


References l.jpg
References

  • Using Oracle9i Materialized Views (Technet Oracle By Example)

    • http://technet.oracle.com/products/oracle9i/htdocs/9iober2/obe9ir2/obe-dwh/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



Requirements for fast refresh l.jpg
Requirements for FAST REFRESH

Willie Albino May 15, 2003


Rqmts for fast refresh cont d l.jpg
Rqmts For FAST REFRESH (cont’d)

Willie Albino May 15, 2003