1 / 13

Using Materialized Views

Using Materialized Views. Objectives. After completing this lesson, you should be able to: Use materialized views to access remote data using local queries Configure fast refresh for a materialized view. Materialized Views. Materialized views can be created to:

genevieve-parks
Download Presentation

Using Materialized Views

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. Using Materialized Views

  2. Objectives • After completing this lesson, you should be able to: • Use materialized views to access remote data using local queries • Configure fast refresh for a materialized view

  3. Materialized Views • Materialized views can be created to: • Replicate data to remote databases • Cache expensive queries in data warehouse environments • A materialized view is a copy of a table or query result set from a single point in time. • You can select data from a materialized view as you would from a table or view. • The data in a materialized view can be synchronized with the source tables either manually or automatically at scheduled intervals.

  4. Detroit Pittsburgh Refreshing Materialized Views • To keep a materialized view up to date, it needs to be refreshed. • There are three different ways to refresh a materialized view: • Complete • Fast • Force

  5. Materialized View Logs • Materialized view logs are used to support fast refresh for different types of materialized views. • Materialized views which cannot fast refresh are calledcomplex.

  6. Primary Key Materialized Views • Uses the primary key of the source table to identify changed rows • Default type of materialized view • Requires an enabled primary key constraint on the source table CREATE MATERIALIZED VIEW job_mv REFRESH FAST START WITH sysdate NEXT sysdate + 1 WITH PRIMARY KEY AS SELECT * FROM jobs@detroit;

  7. Creating Materialized Views

  8. Detroit Pittsburgh Updatable Materialized Views • Updatable materialized views allow changes made at both the source site and the materialized view site to be synchronized: • Materialized view changes are pushed up to the source site during refreshing. • Changes to the source site are pulled down from source site during refreshing. Pull Push

  9. Monitoring Materialized View Refresh • Check the Database page for alerts: • View materialized view details:

  10. Refresh Job Errors

  11. Monitoring Refresh Operations SELECT owner, mview_name, last_refresh_date FROM DBA_MVIEWS; OWN MVIEW_NAME LAST_REFRESH_DATE --- ---------------------- ----------------- SH CAL_MONTH_SALES_MV 21-JAN-2004 12:48 HR SITE1_JOBS 07-FEB-2004 07:53 SELECT job, what, next_date, broken, failures FROM DBA_JOBS WHERE schema_user='HR'; JOB WHAT ---- ---------------------------------------- NEXT_DATE BROKEN FAILURES --------- -------- ---------- 23 dbms_refresh.refresh('"HR"."SITE1_JOBS"'); 07-FEB-04 N 1

  12. Additional Distributed Data Technologies • Oracle Change Data Capture • Oracle Advanced Queuing • Oracle Streams • Gateway products and Generic Connectivity

  13. Summary • In this lesson, you should have learned about: • Using materialized views to access remote data using local queries • Configuring fast refresh for a materialized view

More Related