VIEW vs. MATERIALIZED VIEW

VIEW

An Oracle VIEW, in essence, is a virtual table that does not physically exist. Rather, it is created by a query joining one or more tables.

The syntax for the CREATE VIEW Statement in Oracle/PLSQL is:

CREATE VIEW view_name AS

This Oracle CREATE VIEW example would create a virtual table based on the result set of the SELECT statement.


You can modify the definition of an Oracle VIEW without dropping it by using the Oracle CREATE OR REPLACE VIEW Statement.

CREATE OR REPLACE VIEW view_name AS

This Oracle CREATE OR REPLACE VIEW example would update the definition of the Oracle VIEW without dropping it. If the Oracle VIEW did not yet exist, the VIEW would merely be created for the first time.


Once an Oracle VIEW has been created, you can drop it with the Oracle DROP VIEW Statement.

DROP VIEW view_name;

This Oracle DROP VIEW example would drop/delete the Oracle VIEW.


A join view is one whose view subquery contains a join. If at least one column in the join has a unique index, then it may be possible to modify one base table in a join view. You can query USER_UPDATABLE_COLUMNS to see whether the columns in a join view are updatable.

SELECT column_name, updatable 
FROM user_updatable_columns    
WHERE table_name = 'LOCATIONS_VIEW';

MATERIALIZED VIEW

A materialized view in Oracle is a database object that contains the results of a query. They are local copies of data located remotely, or are used to create summary tables based on aggregations of a table’s data. Materialized views, which store data based on remote tables are also, know as snapshots.

A materialized view can query tables, views, and other materialized views. Collectively these are called master tables (a replication term) or detail tables (a data warehouse term).

For replication purposes, materialized views allow you to maintain copies of remote data on your local node. These copies are read-only. If you want to update the local copies, you have to use the Advanced Replication feature. You can select data from a materialized view as you would from a table or view.

For data warehousing purposes, the materialized views commonly created are aggregate views, single-table aggregate views, and join views.

In replication environments, the materialized views commonly created are primary key, rowid, and subquery materialized views.

Primary Key Materialized Views

The following statement creates the primary-key materialized view on the table emp located on a remote database.

CREATE MATERIALIZED VIEW mv_pk
    REFRESH FAST START WITH SYSDATE 
 NEXT  SYSDATE + 1/24
 WITH PRIMARY KEY 
 AS SELECT * FROM someTable;

Note: When you create a materialized view using the FAST option you will need to create a view log on the master tables(s) as shown below:

CREATE MATERIALIZED VIEW LOG ON someTable;

Rowid Materialized Views

The following statement creates the rowid materialized view on table emp located on a remote database:

CREATE MATERIALIZED VIEW mv_rowid 
 REFRESH WITH ROWID 
    AS SELECT * FROM someTable;

Subquery Materialized Views

The following statement creates a subquery materialized view based on the emp and dept tables located on the remote database:

CREATE MATERIALIZED VIEW  mv_some
AS SELECT * FROM someTable e
WHERE EXISTS
     (SELECT * FROM anotherTable d
     WHERE e.id = d.id)

REFRESH CLAUSE

refresh [fast|complete|force] 
         [on demand | commit] 
         [start with date] 
         [next date] 
         [with {primary key|rowid}]
	

The refresh option specifies:

  1. The refresh method used by Oracle to refresh data in materialized view
  2. Whether the view is primary key based or row-id based
  3. The time and interval at which the view is to be refreshed

Refresh Method – FAST Clause

The FAST refreshes use the materialized view logs (as seen above) to send the rows that have changed from master tables to the materialized view.

You should create a materialized view log for the master tables if you specify the REFRESH FAST clause.

Materialized views are not eligible for fast refresh if the defined subquery contains an analytic function.

Refresh Method – COMPLETE Clause

The complete refresh re-creates the entire materialized view. If you request a complete refresh, Oracle performs a complete refresh even if a fast refresh is possible.

Refresh Method – FORCE Clause

When you specify a FORCE clause, Oracle will perform a fast refresh if one is possible or a complete refresh otherwise. If you do not specify a refresh method (FAST, COMPLETE, or FORCE), FORCE is the default.

PRIMARY KEY and ROWID Clause

WITH PRIMARY KEY is used to create a primary key materialized view i.e. the materialized view is based on the primary key of the master table instead of ROWID (for ROWID clause). PRIMARY KEY is the default option. To use the PRIMARY KEY clause you should have defined PRIMARY KEY on the master table or else you should use ROWID based materialized views.

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

Rowid materialized views should have a single master table and cannot contain any of the following:

  • Distinct or aggregate functions
  • GROUP BY Subqueries , Joins & Set operations

The START WITH clause tells the database when to perform the first replication from the master table to the local base table. It should evaluate to a future point in time. The NEXT clause specifies the interval between refreshes.

A simple example of the Materialized VIEW is next:

CREATE MATERIALIZED VIEW MV_SOMEDATA
 REFRESH COMPLETE
 START WITH sysdate
 NEXT trunc(SYSDATE+1/24,'HH') + 15/1440
 WITH PRIMARY KEY
 AS [SELECT STATEMENT]

Here, complete refreshing will be initialized at 15-th minute of each hour (2:15, 3:15 etc.)

Materialized View thus offers us flexibility of basing a view on PRIMARY KEY or ROWID, specifying refresh methods and specifying time of automatic refreshes.