Data replication with materialized views
This presentation is the property of its rightful owner.
Sponsored Links
1 / 27

Data Replication with Materialized Views PowerPoint PPT Presentation


  • 75 Views
  • Uploaded on
  • Presentation posted in: General

Data Replication with Materialized Views. ISYS 650. What is a Materialized View ?. A materialized view (MV) is a database object that stores the results of a query at a single point in time. Unlike a view, materialized view is not virtual.

Download Presentation

Data Replication with 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.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -

Presentation Transcript


Data replication with materialized views

Data Replication with Materialized Views

ISYS 650


What is a materialized view

What is a Materialized View ?

  • A materialized view (MV) is a database object that stores the results of a query at a single point in time. Unlike a view, materialized view is not virtual.

  • Materialized views are sometimes referred to as snapshots.

  • Materialized views may be stored locally or remotely in other site.

    • Remote materialized view (RMV)


Why use materialized views

Why use materialized views?

  • Support applications that do not require current data or require data valid at a specific point in time (snapshot data).

  • Increase query performance since it contains results of a query.

  • Remote materialized views are an efficient way to replicate data at different sites compared to fully consistent distributed data.

    • Do not require dedicated network connection and network load is reduced.

    • Efficiently support remote users


Materialized view management

Materialized View Management

  • Define materialized view

  • Refresh materialized view

    • Data of a materialized view may be out-of-date and require to be refreshed.

  • Drop materialized view


Types of materialized views

Types of Materialized Views

  • 1. Read-Only Materialized Views

    • Records in the MV cannot be changed by users.

    • Eliminates the possibility of a materialized view introducing data conflicts with the master (base tables).

  • 2. Updatable Materialized Views

    • users can make changes to the data at the materialized view site.

    • Changes made to an updatable materialized view are pushed back to the master during refresh.

    • Oracle only allow RMV to be updatable.


Refresh methods

Refresh Methods

  • 1. Complete Refresh

    • essentially re-creates the materialized view

  • 2. Fast Refresh (Differential Refresh)

    • To perform a fast refresh, first identifies the changes that occurred in the master since the most recent refresh of the materialized view and then applies these changes to the materialized view.

    • Fast refreshes are more efficient than complete refreshes when there are few changes to the master or the view is refreshed frequently.


Materialized view log for fast refresh

Materialized View Log for Fast Refresh

  • A materialized view log is required on a master if you want to perform a fast refresh on materialized views based on the master. The log is used to record changes to the master.

  • The log is designed to record changes to the master since the last refresh, and net changes since the last refresh can be identified.


Fast refresh of a mv

Fast Refresh of a MV


Oracle s implementation of mv

Oracle’s Implementation of MV

  • 1. Primary Key Materialized Views

    • The default type of materialized view.

    • Primary key materialized views allow materialized view master tables to be reorganized without affecting the eligibility of the materialized view for fast refresh.

  • 2. ROWID Materialized Views

    • A ROWID materialized view is based on the physical row identifiers (rowids) of the rows in a master.

    • Ex. Select ROWID, CID, Cnama From Customer;

    • For views based on master tables that do not have a primary key, or for views that do not include all primary key columns of the master tables.


Define a read only primary key materialized views

Define a Read-Only, Primary Key Materialized Views

  • http://psoug.org/reference/materialized_views.html

  • CREATE MATERIALIZED VIEW mv_Customer

    AS SELECT * FROM Customer;

  • Note: Compare view and materialized view


Multiple tables mv

Multiple Tables MV

CREATE MATERIALIZED VIEW "STUDENTUNITS

AS

select sid,sname,sum(units) as TotalUnits

from (student natural join (registration natural join course)) group by sid,sname;


Initiating an on demand refresh

Initiating an On-Demand Refresh

  • On-Demand Refresh

    • Immediately refresh dependent materialized view to propagate the new rows of the master table to associated materialized views.

    • Example:

      • execute DBMS_MVIEW.REFRESH( 'MV‘)

    • Note: ‘MV’ is the view’s name.


Requirements for fast refresh

Requirements for Fast Refresh

  • 1. The base table must have a primary key constraint.

  • 2. Must create an update log.


Define a mv for fast refresh

Define a MV for Fast Refresh

CREATE MATERIALIZED VIEW mv_Customer

REFRESH FAST

AS SELECT * FROM Customer;

Note: Must create a log first.


Create materialized view log for fast refresh

CREATE MATERIALIZED VIEW LOG for Fast Refresh

  • Use the CREATE MATERIALIZED VIEW LOG statement to create a materialized view log, which is a table associated with the master table of a materialized view.

  • A master table can have only one log.

    • The log’s name is: MLOG$_TableName

    • http://www.sqlsnippets.com/en/topic-12878.html

  • Example:

    • create materialized view log on tableName WITH PRIMARY KEY ;

    • create materialized view log on Faculty WITH PRIMARY KEY ;


Log s structure with primary key

Log’s Structure with Primary Key


Log s structure with rowid

Log’s Structure with ROWID


Data replication with materialized views

Demo

  • Make a few changes to the base table.

  • See the log records.

  • See the MV records.

  • Issue a refresh command:

    • execute DBMS_MVIEW.REFRESH( 'MV‘)

  • The Log records will be deleted automatically after the refresh.


Fast refresh on commit

Fast Refresh On Commit

CREATE MATERIALIZED VIEW mv_Customer

REFRESH FAST On Commit

AS SELECT * FROM Customer;

Note: Must create a log first.

Note: MV is refreshed after each update.


Other way to initiate refresh

Other Way to Initiate Refresh

  • Scheduled Refresh

    • An interval of one hour is specifies as:

      • SYSDATE + 1/24

    • An interval of seven days is specifies as:

      • SYSDATE + 7

  • Example:

    • CREATE MATERIALIZED VIEW MVFaculty2

    • REFRESH FAST START WITH SYSDATE NEXT SYSDATE + 1/4096

    • AS SELECT * FROM faculty;


Grant privileges to other user

Grant Privileges to Other User

  • 1. Select the table or view in the Object Browser

  • 2. Click the Grants tab and grant the privileges to other user (grantee).

  • The Grantee can access the data from his/her database by adding the granter’s name:

  • Example: If user HR grants the Select privilege:

    • Select * From HR.Customer;


Mv based on granted table

MV Based on Granted Table

  • Login HR and grant Employees table privilege to dchao. Logout HR

  • Login dchao and create an MV based on HR.Employees. Logout dchao

  • Login HR and make a few changes. Logout HR

  • Login dchao and list records in the MV.

  • Issue a refresh command.


Complex simple materialized view

Complex & Simple Materialized View

  • Simple Materialized View

    • Each row in the materialized view can be mapped back to a single row in a source table

  • Complex Materialized View

    • Each row in the materialized view can not be mapped back to a single row in a source table.


Simple vs complex mv

Simple vs Complex MV

If you refresh rarely and want faster query performance, then use Method A (complex materialized view).

If you refresh regularly and can sacrifice query performance, then use Method B (simple materialized view).


Materialized view concepts and architecture

Materialized View Concepts and Architecture

  • http://download.oracle.com/docs/cd/B10501_01/server.920/a96567/repmview.htm#30769

  • http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/statements_6002.htm

  • http://www.sqlsnippets.com/en/topic-12890.html


Define a rowid materialized views

Define a ROWID Materialized Views

  • CREATE MATERIALIZED VIEW orders

  • REFRESH WITH ROWID

  • AS SELECT * FROM orders;

  • Create log with ROWID:

    • create materialized view log on tableName WITH ROWID;


Differential refresh

Differential Refresh

  • Three kinds of update:

    • Insertion

    • Deletion

    • Modification:

      • Deletion of the before-image, and insertion of the after-image.

  • Differential refresh:

    Let Deletions is the set of the all deleted records, and Insertions is the set of all new records.

    New MV = Old MV – Deletions + Insertions


  • Login