Materialized Views

1
Materialized Views
2
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

3
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

4
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

5
Database Parameter Settings

• init.ora parameter
• COMPATIBLE8.1.0 (or above)
• System or session settings
• query_rewrite_enabledtruefalse
• query_rewrite_integrity
  enforcedtrustedstale_tolerated
• Can be set for a session using
• alter session set query_rewrite_enabledtrue
• alter session set query_rewrite_integrityenforced
  
• 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

6
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

7
Query Rewrite Details (contd)

• 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

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

9
Syntax For Materialized Views

• CREATE MATERIALIZED VIEW ltnamegt
• TABLESPACE lttbs namegt ltstorage parametersgt
• ltbuild optiongt
• REFRESH ltrefresh optiongt ltrefresh modegt
• ENABLEDISABLE QUERY REWRITE
• AS
• SELECT ltselect clausegt
• The ltbuild optiongt 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

10
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

11
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(ltmv_namegt, ltrefresh_optiongt)
• DBMS_MVIEW.REFRESH_ALL_MVIEWS()
• START WITH NEXT ltdategt - refreshes start at a
  specified date/time and continue at regular
  intervals

12
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
13
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
14
Materialized View Example (contd)

• SQLgt ALTER SESSION SET QUERY_REWRITE_INTEGRITYTRU
  STED
• SQLgt ALTER SESSION SET QUERY_REWRITE_ENABLEDFALSE
  
• SQLgt _at_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 013217.93
• Execution Plan
• --------------------------------------------------
  --------
• 0 SELECT STATEMENT OptimizerHINT FIRST_ROWS
  (Cost360829 Card6 Bytes120)
• 1 0 SORT (GROUP BY) (Cost360829 Card6
  Bytes120)
• 2 1 PARTITION RANGE (INLIST
• 3 2 TABLE ACCESS (FULL) OF ITEMS'
  (Cost360077
• Card375154 Bytes7503080)

15
Materialized View Example (contd)
SQLgt ALTER SESSION SET QUERY_REWRITE_ENABLEDTRUE
SQLgt _at_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
000140.47 Execution Plan ----------------------
--------------------------------------------------
---------------------- 0 SELECT STATEMENT
OptimizerHINT FIRST_ROWS (Cost3749 Card12
Bytes276) 1 0 SORT (GROUP BY) (Cost3749
Card12 Bytes276) 2 1 PARTITION RANGE
(INLIST) 3 2 TABLE ACCESS (FULL) OF
ITEMS_SUMMARY_MV' (Cost3723
Card7331 Bytes168613)
16
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
17
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_NAMEITEMS_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
18
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_NAMEITEMS_SUMMARY_MV POSITION
COLUMN AGG_FUNCTION --------
----------------- ------------ 6
GMS SUM 7 NET_REV
SUM 11 QTY_SOLD
SUM 12 UNITS
COUNT
19
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

20
Syntax For Creating A Dimension
CREATE DIMENSION ltdimension namegt LEVEL
ltlevelgt IS ltlevel_table.level_columngt ltlevelgt
IS ltlevel_table.level_columngt HIERARCHY
lthierarchy_namegt ( ltchild_levelgt CHILD OF
ltparent_levelgt ltchild_levelgt CHILD OF
ltparent_levelgt ATTRIBUTE ltlevelgt DETERMINES
ltdependent_columngt ltlevelgt DETERMINES
ltdependent_columngt,) To validate a
dimension exec dbms_olap.validate_dimension(ltdim_
namegt,ltownergt,FALSE,FALSE)
21
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
22
Example of Validating A Dimension
SQLgt exec dbms_olap.validate_dimension(time_dim,
USER, FALSE, FALSE) PL/SQL procedure
successfully completed. SQLgt 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)
23
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_datec.cal_date GROUP BY l.slr_id,
c.cal_date
24
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 SQLgt select c.qtr_id, sum(l.gms) gms
2 from items l, calendar c 3 where
l.end_datec.cal_date 4 group by l.slr_id,
c.qtr_id Execution Plan ------------------------
---------------------------------- SELECT
STATEMENT OptimizerCHOOSE (Cost16174 Card36258
Bytes1160256) SORT (GROUP BY) (Cost16174
Card36258 Bytes1160256) HASH JOIN (Cost81
Card5611339 Bytes179562848) TABLE ACCESS
(FULL) OF CALENDAR' (Cost2 Card8017
Bytes128272) TABLE ACCESS (FULL) OF ITEMS'
(Cost76 Card69993 Bytes1119888)
25
Example of Using Dimensions (contd)
-- Step 3 of 4 Create time dimension (see slide
21 for SQL) _at_cr_time_dim.sql Dimension
Created -- Step 4 of 4 Rerun query based on
quarter with time dimension SQLgt select
c.qtr_id, sum(l.gms) gms 2 from items l,
calendar c 3 where l.end_datec.cal_date 4
group by l.slr_id, c.qtr_id Execution
Plan ---------------------------------------------
------------- SELECT STATEMENT OptimizerCHOOSE
(Cost3703 Card878824 Bytes44820024) SORT
(GROUP BY) (Cost3703 Card878824
Bytes44820024) HASH JOIN (Cost31 Card878824
Bytes44820024) VIEW (Cost25 Card8017
Bytes128272) SORT (UNIQUE) (Cost25
Card8017 Bytes128272) TABLE ACCESS (FULL)
OF CALENDAR (Cost2 Card8017 Bytes128272)
TABLE ACCESS (FULL) OF ITEMS_MV (Cost3
Card10962 Bytes383670)
26
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

27
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_vi
  ews.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
  

28
(No Transcript)
29
Requirements for FAST REFRESH
30
Rqmts For FAST REFRESH (contd)