MATERIALIZED VIEWS: Over Coming Limitations

1
MATERIALIZED VIEWS Over Coming Limitations
2
Agenda

• Overview of Materialized Views
• Benefits of Using Materialized Views
• Extending Materialized Views
• Summary

3
To create a Materialized View

• create materialized view SALES_MONTH_MV
• tablespace AGG_DATA
• refresh complete
• start with sysdate
• next sysdate1
• enable query rewrite
• as
• select Sales_Month, SUM(Amount)
• from SALES
• Group by Sales_Month

4
Primary Key Materialized Views

• SQLgt CREATE MATERIALIZED VIEW mv_emp_pk
  REFRESH FAST START WITH SYSDATE NEXT
  SYSDATE 1/48 WITH PRIMARY KEY AS SELECT
  FROM emp_at_remote_db 

5
To create a view log

• SQLgt CREATE MATERIALIZED VIEW LOG ON emp
• Materialized view log created

6
ROWID Materialized Views

• SQLgt CREATE MATERIALIZED VIEW mv_emp_rowid
  REFRESH WITH ROWID AS SELECT FROM
  emp_at_remote_db

7
Subquery Materialized Views

• SQLgt CREATE MATERIALIZED VIEW mv_empdept
  AS SELECT
• FROM emp_at_remote_db e
  WHERE EXISTS (SELECT
• FROM dept_at_remote_db d
  WHERE e.dept_no
  d.dept_no)

8
REFRESH CLAUSE

• refresh fastcompleteforce
• on demand commit
• start with date next date
• with primary keyrowid

9
Timing the refresh

• SQLgt CREATE MATERIALIZED VIEW mv_emp_pk
  REFRESH FAST START WITH SYSDATE NEXT
  SYSDATE 2 WITH PRIMARY KEY AS SELECT
  FROM emp_at_remote_db

10
Benefits of Materialized Views

• Less physical reads
• Less writes
• Decreased CPU consumption
• Markedly faster response times

11
Constraints

• Alter session set query_rewrite_integrity
  trusted
• Alter table dept
• add constraint dept_pk primary key(deptno)
• rely enable NOVALIDATE

12
Dimensions

• SQLgt CREATE dimension time_hierarchy_dim
• level day is time_hierarchy.day
• level mmyyyy is
  time_hierarchy.mmyyyy
• level qtr_yyyy is
  time_hierarchy.qtr_yyyy
• level yyyy is time_hierarchy.yyyy
• hierarchy time_rollup
• (
• day child of
• mmyyyy child of
• qtr_yyyy child of
• yyyy
• )
• attribute mmyyyy
• determines mon_yyyy

13
Limitations of Materialized Views

• The defining query of the materialized view
  cannot contain any non-repeatable expressions
• The query cannot contain any references to RAW or
  LONG RAW data types or object REFs.
• If the defining query of the materialized view
  contains set operators (UNION, MINUS, and so on),
  rewrite will use them for full text match rewrite
  only.
• If the materialized view was registered as
  PREBUILT, the precision of the columns must agree
  with the precision of the corresponding SELECT
  expressions unless overridden by the WITH REDUCED
  PRECISION clause.
• If the materialized view contains the same table
  more than once, it is possible to do a general
  rewrite, provided the query has the same aliases
  for the duplicate tables as the materialized view
• Complete refreshes over slow or bad connections
  can sometimes never finish

14
ORA-01555 Explanation

• CASE 1 - ROLLBACK OVERWRITTEN
• Session 1 starts query at time T1 and QENV 50
• Session 1 selects block B1 during this query
• Session 1 updates the block at SCN 51
• Session 1 does some other work that generates
  rollback information.
• Session 1 commits the changes made in steps '3'
  and '4'. (Now other transactions are free to
  overwrite this rollback information)
• Session 1 revisits the same block B1 (perhaps for
  a different row).

15
CASE 2 - ROLLBACK TRANSACTION SLOT OVERWRITTEN

• Session 1 starts query at time T1 and QENV 50
• Session 1 selects block B1 during this query
• Session 1 updates the block at SCN 51
• Session 1 commits the changes (Now other
  transactions are free to overwrite this rollback
  information)
• A session (Session 1, another session or a number
  of other sessions) then use the same rollback
  segment for a series of committed transactions.
  These transactions each consume a slot in the
  rollback segment transaction table such that it
  eventually wraps around (the slots are written to
  in a circular fashion) and overwrites all the
  slots. Note that Oracle is free to reuse these
  slots since all transactions are committed.
• Session 1's query then visits a block that has
  been changed since the initial QENV was
  established. Oracle therefore needs to derive an
  image of the block as at that point in time.

16
Solutions

• CASE 1 - ROLLBACK OVERWRITTEN
• Increase size of rollback segment which will
  reduce the likelihood of overwriting rollback
  information that is needed.
• Reduce the number of commits (same reason as 1).
• Run the processing against a range of data rather
  than the whole table. (Same reason as 1).
• Add additional rollback segments. This will allow
  the updates etc. to be spread across more
  rollback segments thereby reducing the chances of
  overwriting required rollback information.
• If fetching across commits, the code can be
  changed so that this is not done

17
CASE 2 - ROLLBACK TRANSACTION SLOT OVERWRITTEN

• Use any of the methods outlined above. This will
  allow transactions to spread their work across
  multiple rollback segments therefore reducing the
  likelihood or rollback segment transaction table
  slots being consumed.
• If it is suspected that the block cleanout
  variant is the cause, then force block cleanout
  to occur prior to the transaction that returns
  the ORA-1555. This can be achieved by issuing the
  following in SQLPlus, SQLDBA or Server
  Manager
• alter session set optimizer_goal
  rule select count() from
  table_name
• If indexes are being accessed then the problem
  may be an index block and clean out can be forced
  by ensuring that all the index is traversed. For
  example, if the index is on a numeric column with
  a minimum value of 25 then the following query
  will force cleanout of the index
• select index_column from
  table_name
• where index_column gt 24

18
Over Coming Limitations
Create XSNAPSHOT create table T (a integer
primary key, b long)create snapshot log on
Tcreate_xsnapshot_log('T')create table ST as
select a from Talter table ST add b
longcreate snapshot ST on prebuilt
table        refresh fast on demand as select a
from Tcreate_xsnapshot('ST','B','B')refresh_xs
napshot('ST','C') And to execute a fast
refreshrefresh_xsnapshot('ST','F')
19
Over Coming Limitations
A normal, complete Oracle refresh essentially
does this        insert into ST select from
TBradmark uses an interval copy to make
refreshes more robust.  An interval copy
essentially does this        insert into ST
select from T where a between 1 and
100        insert into ST select from T where
a between 101 and 200        ...        insert
into ST select from T where a between 901 and
1000
20
Summary

• Materialized Views offer us flexibility of basing
  a view on Primary key or ROWID, specifying
  refresh methods and specifying time of automatic
  refreshes.
• Users, Applications, Developers and others can
  take advantage of the fact that the answer has
  already been stored for them.
• Tools such as the DBMS_OLAP Package allow for
  easier maintenance.
• In a read-only / read-intensive environment will
  provide reduced query response time and reduced
  resources needed to actually process the queries.