materialized views
Download
Skip this Video
Download Presentation
Materialized Views

Loading in 2 Seconds...

play fullscreen
1 / 30

materialized views - PowerPoint PPT Presentation


  • 337 Views
  • Uploaded 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

Materialized Views

Willie Albino May 15, 2003

materialized views agenda
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
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 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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
Requirements for FAST REFRESH

Willie Albino May 15, 2003

rqmts for fast refresh cont d
Rqmts For FAST REFRESH (cont’d)

Willie Albino May 15, 2003

ad