Data Replication with Materialized Views

1. Data Replication with Materialized Views ISYS 650

2. 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)

3. 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

4. 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

5. 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.

6. 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.

7. 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.

8. Fast Refresh of a MV

9. 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.

10. 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

11. 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;

12. 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.

13. Requirements for Fast Refresh • 1. The base table must have a primary key constraint. • 2. Must create an update log.

14. Define a MV for Fast Refresh CREATE MATERIALIZED VIEW mv_Customer REFRESH FAST AS SELECT * FROM Customer; Note: Must create a log first.

15. 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 ;

16. Log’s Structure with Primary Key

17. Log’s Structure with ROWID

18. 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.

19. 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.

20. 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;

21. 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;

22. 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.

23. 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.

24. 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).

25. 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

26. 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;

27. 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