Characterization of Materialized views and its types in Oracle10g

Oracle provides a database object known as materialized views to replicate copies of data in the database. The replication can be done on part or all of a table, or for the result of a query on multiple tables. The time intervals in which the replicated data is refreshed can be specified.

The objective of replication of data, like creation of local copies of data presiding in remote database is to improve the performance of distributed queries. We can also restrict the rows or columns returned by specifying conditions or generate new columns by applying functions.

The network loads can be reduced by replicating data across multiple database servers. The whole data replication process including refresh is automated. There is also an option to manually do a refresh on demand.

To create a materialized view, the required system privileges must be given depending on the underlying tables accessed and the options used. Materialized views can be Read-Only, Updateable or Writeable.

A Read only materialized views does not allows Data manipulation language commands to be executed. When transactions occur in the master table the data is changed correspondingly in the materialized view. Hence there will not be any data conflicts between master table and the materialized view.

In an updateable materialized view, data will be changed based on the data in the master table or directly by the users in the materialized view site. So data has to be refreshed both from master to materialized view and vice versa. This helps to decrease load on master site but this may lead to data conflicts, for example; if data in the materialized view of a particular record is deleted and this is corresponding to data in the master site where there is a record present in another table that references this record (via a foreign key) this causes data integrity problem and has to be reconciled.

In a writeable materialized view, data will be changed based on the data in the master table or directly by the users in the materialized view site. But the data refresh is one sided. Changes made in the materialized view will not be reflected in master. These are rarely used.

Oracle offers many types of materialized views that can be used based on replication situation required.

The Primary key Materialized views are the default type. Here the changes are refreshed according to the changes that have occurred at the row level and identified by the primary key value. These may be created as a subset by having a sub query in the where clause of the create statement. An object materialized view is created based on an object table and each row object is identified by an object identifier column.

Rowid Materialized views are only applicable for views based on master tables residing in an Oracle7 database. It is based on the physical row identifiers of the rows in the master. Complex Materialized views will have a defining query that is general and does not follow the usual restrictions for the material views with sub queries. It may have a connect by clause, set operation or aggregate function.

The data in a materialized view can be replicated once or at intervals. The refresh can be either manual or automatic. The refresh option in the creation of materialized view command specifies the mechanism to be used by oracle while refreshing the materialized view.

There are three options available. They are fast, complete and force. Fast refreshes are only possible if base table data can be matched one to one with the corresponding materialized view. The complete option will do refresh by truncating the data in the materialized view and re-executing the materialized view’s base query to populate it. If the force option is specified, oracle will use a fast refresh if it is available; otherwise a complete refresh will be used.

With Oracle10g there is an extended refresh mechanism concept called nested refresh that is available. A nested materialized view has underlying materialized views that are refreshed based on dependency order and the nested materialized view itself is refreshed to be consistent with underlying tables as and when possible.

| Characterization of Materialized views and its types in Oracle10g | Introduction to Capability Maturity Model (CMM) | Introduction to Data Blocks, Extents and Segments –Logical Storage Structures in Oracle 10g Server | Oracle Application Server10g – Business Intelligence Services | Oracle Application Server10g – Content Management Services | Overview of Commit and Rollback – The Transaction Control Statements in Oracle 10g | Overview of Flashback Features in Oracle10g | Overview on Database Change Notification in Oracle10g | Overview on Implicit and Explicit Cursors in PL/SQL | Overview on Sequential Control and Conditional Control Statements in PL/SQL | Overview on some of the new features in Oracle Database 10g | Overview on the usage of Cursor Variables in PL/SQL | Performance Tuning in Oracle10g – Best Practices in Application Development | Representing Conditional Expressions as Data in Oracle10g | Understanding Indexes and their key benefits in Oracle10g | Understanding Oracle10g Application Server –Portal Services |