1 / 22

Oracle Materialized V iews for Replication

Oracle Materialized V iews for Replication. COUG Presentation, Feb 20, 2014. Jane Lamont, jlamont@geologic.com. Materialized Views 101. Types and uses of materialized views Basic setup of materialized views Common types of refreshes used on materialized views. Demo setup, refresh and drop.

bairn
Download Presentation

Oracle Materialized V iews for Replication

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. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Oracle Materialized Views for Replication COUG Presentation, Feb 20, 2014 Jane Lamont, jlamont@geologic.com

  2. Materialized Views 101 • Types and uses of materialized views • Basic setup of materialized views • Common types of refreshes used on materialized views. • Demo setup, refresh and drop

  3. Bio • 17 years experience working with Oracle databases in ON, NT, and AB • Certified OCP - 7, 8, 9i, 11g and E-Business Suite, 11g • Instructor at SAIT, BA Program & DBA Fast-Track Program, 2001 - 2005 • Executive of COUG 2000 – 2008 and President 2003-2006 • Presented twice before at COUG • Built and maintained > 3000 materialized views 5 to 25M records in each in a warehousing environment at geoLOGIC Systems for past 4 years

  4. Data Warehouse Architecture

  5. Views & Materialized Views • Views are based on a query where the structure may/may not be saved, and the results are cached only • Materialized views are based on a query that is saved and where the results reside in physical tables • Snapshot is a previous term for materialized view. Will still see reference to snapshots in Oracle docs

  6. Materialized Views • A materialized view (mview) is a replica of a master table from a single point in time connected together via database links. • Mviews are updated from one or more masters through individual batch updates, called refreshes. • Fast refresh is applying only changes to the master site to the mview, enabled by a materialized view log that records the changes to the master table. • Complete refresh is a full copy of the master site to the mview.

  7. Materialized View Replication

  8. Why Use Materialized Views? • Ease Network Loads. Distribute the corporate database amongst multiple sites, giving stable location for client connection while staging area is updating/processing. • Create a Mass Deployment Environment. Rollout db infrastructure quickly and easily • Enable Data Subsetting or Aggregation. Query of master table(s). Query rewriting by the optimizer. • Enable Disconnect Computing. No need for dedicated network connection between databases

  9. Materialized Views • Read-only • Updatable, must belong to group.

  10. Types of Materialized Views • Primary Key – default. Based on the pk in the master. • Object – based on object table and created using the OF type clause. • ROWID – based on the rowids in the master • Complex – if defining query does not meet restrictions to be fast refreshed, such as CONNECT BY, INTERSECT, MINUS, UNION ALL etc. can only be refreshed ‘complete’.

  11. Materialized View Security • Privileges • CREATE MATERIALIZED VIEW • CREATE TABLE • SELECT object privilege on master table and its mviewlog, if not using database link, otherwise is included in the link

  12. Mviewlog in the Staging Database • A master table on which the MView is based onhas a MViewlog table (MLOG$_ ) to hold the changed rows in the master table • Analogous to the redo log. • An entry in SYS.MLOG$ defines the MView log. • A fast refresh is based on the rowids or primary keys • Note: If the mviewquery is NOT simple then it cannot be fast refreshed so it will NOT need a mviewlog. • One master table/mview log can have > 1 mviews. Log ensures that all mviews are refreshed and does not purge itself until all mviews are refreshed. • Also mviews may be refreshed from the same mview log at different times so they are kept in synch by the timestamp of the fast refresh. 11gR2 now uses commit SCN data instead of timestamps which improves the speed of the mview refresh. • SQL> create materialized log on emp with commit scn;

  13. Mviews in the Warehouse • A table in the mview site is referred to as the mviewbase table. • An unique index on the mviewbase table • An entry in SYS.SNAP$ defining the mview. • An entry in SYS.SLOG$ at master site. • SQL>create materialized view emp_mv refresh fast on demand as select * from scott.emp@oracle.world; • Note: if complete refresh, set the mview PCTFREE to 0 and PCTUSED to 99 for maximum efficiency

  14. DBMS_MVIEW supplied package • Various procedures to design, build, troubleshoot, repair mviews • Refresh procedure • Explain_mview procedure • Purge_log procedure • Register_mview procedure

  15. MviewRefresh Procedure • DBMS_MVIEW.REFRESH(‘<table_name>’, ‘COMPLETE’); • Complete transfer of data from the master table to mview base table • List of tables refreshed in single transaction, consistency across the mviews to a single point in time and if 1 errors, none are refreshed, add parameter: atomic_refresh=true • DBMS_MVIEW.REFRESH(‘emp_mv, dept_mv’, ’complete’, atomic_refresh=true); • DBMS_MVIEW.REFRESH(‘<table_name>’, ‘FAST’); • Changes contained in the mview log are applied to the mview base table.

  16. Idea: Mviewused for cut-over • If time is limited to do a cut-over to a new database. Lots of time to prep, no time to execute: • RMAN cloning • Standby database • Mview drop with preserve table (includes indexes) clause • SQL> drop materialized view on emp preserve table;

  17. References • Oracle Database Concepts, 11.2 Release • E40540-01 • Oracle Database Advanced Replication, 11.2 Release • E10706-06 • Oracle Database PL/SQL Packages and Types Reference, 11.2 Release • E40758-03

  18. My experiences • Do not data pump mviews to new database, it loses its registration in the master site, in the SYS.SLOG$. • Logs can be ‘pesky’ and need to be rebuilt. Do complete refreshes to all mviews first! • Logs keep filling and never purging. • Bug in 11gR2 where master table was in a 11gR2 version and mview in 11gR1 version. • Watch out for the database links if moving either master or base table.

  19. Demo • 1 table replicated from OLTP or batch master table site in the staging schema to a warehouse schema using a mviewbased on the primary key of the master table. Then SQL SELECT done against the warehouse • Mview will be complete refreshed automatically when built, then will be fast refreshed on demand as data received into the staging database • MviewLog will use commit scn • Mview will be dropped preserving the table

  20. Demo Scott, data analyst, connects to warehouse Master table, Sales and log in HQ schema MView, Sales_mv in WH schema

  21. Summary • Consider using mviews in a situation where an end user will need data refreshed periodically

  22. Q & A

More Related