MATERIALIZED VIEWS Over Coming Limitations Agenda Overview of

  • Slides: 20
Download presentation
MATERIALIZED VIEWS: Over Coming Limitations

MATERIALIZED VIEWS: Over Coming Limitations

Agenda Ø Overview of Materialized Views Ø Benefits of Using Materialized Views Ø Extending

Agenda Ø Overview of Materialized Views Ø Benefits of Using Materialized Views Ø Extending Materialized Views Ø Summary

To create a Materialized View create materialized view SALES_MONTH_MV tablespace AGG_DATA refresh complete start

To create a Materialized View create materialized view SALES_MONTH_MV tablespace AGG_DATA refresh complete start with sysdate next sysdate+1 enable query rewrite as select Sales_Month, SUM(Amount) from SALES Group by Sales_Month;

Primary Key Materialized Views SQL> CREATE MATERIALIZED VIEW mv_emp_pk REFRESH FAST START WITH SYSDATE

Primary Key Materialized Views SQL> CREATE MATERIALIZED VIEW mv_emp_pk REFRESH FAST START WITH SYSDATE NEXT SYSDATE + 1/48 WITH PRIMARY KEY AS SELECT * FROM emp@remote_db;

To create a view log SQL> CREATE MATERIALIZED VIEW LOG ON emp; Materialized view

To create a view log SQL> CREATE MATERIALIZED VIEW LOG ON emp; Materialized view log created

ROWID Materialized Views SQL> CREATE MATERIALIZED VIEW mv_emp_rowid REFRESH WITH ROWID AS SELECT *

ROWID Materialized Views SQL> CREATE MATERIALIZED VIEW mv_emp_rowid REFRESH WITH ROWID AS SELECT * FROM emp@remote_db;

Subquery Materialized Views SQL> CREATE MATERIALIZED VIEW mv_empdept AS SELECT * FROM emp@remote_db e

Subquery Materialized Views SQL> CREATE MATERIALIZED VIEW mv_empdept AS SELECT * FROM emp@remote_db e WHERE EXISTS (SELECT * FROM dept@remote_db d WHERE e. dept_no = d. dept_no)

REFRESH CLAUSE [refresh] [fast|complete|force] [on demand | commit] [start with date] [next date] [with

REFRESH CLAUSE [refresh] [fast|complete|force] [on demand | commit] [start with date] [next date] [with {primary key|rowid}]]

Timing the refresh SQL> CREATE MATERIALIZED VIEW mv_emp_pk REFRESH FAST START WITH SYSDATE NEXT

Timing the refresh SQL> CREATE MATERIALIZED VIEW mv_emp_pk REFRESH FAST START WITH SYSDATE NEXT SYSDATE + 2 WITH PRIMARY KEY AS SELECT * FROM emp@remote_db;

Benefits of Materialized Views Ø Ø Less physical reads Less writes Decreased CPU consumption

Benefits of Materialized Views Ø Ø Less physical reads Less writes Decreased CPU consumption Markedly faster response times

Constraints Ø Alter session set query_rewrite_integrity = trusted Ø Alter table dept add constraint

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

Dimensions SQL> CREATE dimension time_hierarchy_dim level day is time_hierarchy. day level mmyyyy is time_hierarchy.

Dimensions SQL> 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 ;

Limitations of Materialized Views 1. The defining query of the materialized view cannot contain

Limitations of Materialized Views 1. The defining query of the materialized view cannot contain any non-repeatable expressions 2. The query cannot contain any references to RAW or LONG RAW data types or object REFs. 3. 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. 4. 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. 5. 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 6. Complete refreshes over slow or bad connections can sometimes never finish

ORA-01555 Explanation CASE 1 - ROLLBACK OVERWRITTEN 1. 2. 3. 4. 5. 6. Session

ORA-01555 Explanation CASE 1 - ROLLBACK OVERWRITTEN 1. 2. 3. 4. 5. 6. Session 1 starts query at time T 1 and QENV 50 Session 1 selects block B 1 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 B 1 (perhaps for a different row).

CASE 2 - ROLLBACK TRANSACTION SLOT OVERWRITTEN 1. 2. 3. 4. 5. 6. Session

CASE 2 - ROLLBACK TRANSACTION SLOT OVERWRITTEN 1. 2. 3. 4. 5. 6. Session 1 starts query at time T 1 and QENV 50 Session 1 selects block B 1 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.

Solutions CASE 1 - ROLLBACK OVERWRITTEN 1. Increase size of rollback segment which will

Solutions CASE 1 - ROLLBACK OVERWRITTEN 1. Increase size of rollback segment which will reduce the likelihood of overwriting rollback information that is needed. 2. Reduce the number of commits (same reason as 1). 3. Run the processing against a range of data rather than the whole table. (Same reason as 1). 4. 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. 5. If fetching across commits, the code can be changed so that this is not done

CASE 2 - ROLLBACK TRANSACTION SLOT OVERWRITTEN 1. Use any of the methods outlined

CASE 2 - ROLLBACK TRANSACTION SLOT OVERWRITTEN 1. 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. 2. 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 SQL*Plus, SQL*DBA or Server Manager: alter session set optimizer_goal = rule; select count(*) from table_name 3. 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 > 24;

Over Coming Limitations Create XSNAPSHOT create table T (a integer primary key, b long);

Over Coming Limitations Create XSNAPSHOT create table T (a integer primary key, b long); create snapshot log on T; create_xsnapshot_log('T'); create table ST as select a from T; alter table ST add b long; create snapshot ST on prebuilt table refresh fast on demand as select a from T; create_xsnapshot('ST', 'B'); refresh_xsnapshot('ST', 'C'); And to execute a fast refresh: refresh_xsnapshot('ST', 'F');

Over Coming Limitations A normal, complete Oracle refresh essentially does this: insert into ST

Over Coming Limitations A normal, complete Oracle refresh essentially does this: insert into ST select * from T Bradmark 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

Summary Ø Materialized Views offer us flexibility of basing a view on Primary key

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.