Materialized Views - Oracle to SQL Server Migration

In Oracle, CREATE MATERIALIZED VIEW statement creates a view that stores the query result similar to a table that stores its rows.

You can specify when to refresh the data in a materialized view: when changes to the underlying objects are committed, on demand, at specified time intervals, or never refresh.

Materialized views can be used to

  • Increase the performance of a query containing multiple joins, aggregate functions i.e. by calculating the result set in advance
  • Replicate data from a remote database on a time-interval basis

Oracle:

  -- The query of the view joins 2 tables and the result will be stored
  CREATE MATERIALIZED VIEW cities_v 
  AS
  SELECT c.name AS city, s.name AS state
  FROM cities c, states s 
  WHERE c.state = s.id;

Materialized View Overview

Summary information:

Data Data is stored and storage space is consumed
Default Refresh ON DEMAND Use DBMS_MVIEW procedures to refresh
Query Rewrite Allows to increase performance without changing the queries in application

Materialized View Details

Assume there are states and cities tables:

Oracle:

  -- Table definitions
  CREATE TABLE states
  ( 
     id CHAR(2),
     name VARCHAR(90)
  );
 
  CREATE TABLE cities
  (
     name VARCHAR(90),
     state CHAR(2)
  );
 
  -- Data
  INSERT INTO states VALUES ('CA', 'California');
 
  INSERT INTO cities VALUES ('San Francisco', 'CA');
  INSERT INTO cities VALUES ('San Diego', 'CA');

Now let's create a materialized view that retrieves cities and the full name of the state:

Oracle:

  CREATE MATERIALIZED VIEW cities_v 
  AS
  SELECT c.name AS city, s.name AS state
  FROM cities c, states s 
  WHERE c.state = s.id;

When you select rows from cities_v Oracle does not need to perform a join:

  -- Enable trace in SQL*Plus to see statistics
  SET AUTOTRACE ON
 
  SELECT * FROM cities_v;
  # San Francisco   California
  # San Diego         California
 
  -- Fragment of statistics returned by SQL*Plus after executing the query
  # Execution Plan
  # 0 SELECT STATEMENT  
  # 1   MAT_VIEW ACCESS FULL | CITIES_V

How to Improve Query Performance Without Source Code Changes

Often you do not have access to the source code of the application, so you cannot change a query in it to use a materialized view.

The application still executes a join, and the materialized view is not used:

Oracle:

  -- Application still contains a SQL SELECT with join
  SELECT c.name AS city, s.name AS state
  FROM cities c, states s 
  WHERE c.state = s.id;
  # San Francisco   California
  # San Diego         California
 
  -- Fragment of statistics returned by SQL*Plus after executing the query
  # Execution Plan
  # 0 SELECT STATEMENT
  # 1   HASH JOIN     
  # 2      TABLE ACCESS FULL | STATES
  # 3      TABLE ACCESS FULL | CITIES

But if you specify ENABLE QUERY REWRITE option when you create a materialized view, Oracle can use it even if the application still executes the original statement containing joins, aggregates:

  -- Define a materialized view with QUERY REWRITE option
  CREATE MATERIALIZED VIEW cities_v_query_rewrite 
    ENABLE QUERY REWRITE
  AS
  SELECT c.name AS city, s.name AS state
  FROM cities c, states s 
  WHERE c.state = s.id;

Now when you execute the original query, the Oracle optimizer automatically recognizes that the materialized view can be used, and no joins are performed:

  -- Application still contains a SQL SELECT with join
  SELECT c.name AS city, s.name AS state
  FROM cities c, states s 
  WHERE c.state = s.id;
  # San Francisco   California
  # San Diego         California
 
  -- Fragment of statistics returned by SQL*Plus after executing the query
  # Execution Plan
  # 0 SELECT STATEMENT
  # 1   MAT_VIEW REWRITE ACCESS FULL | CITIES_V_QUERY_REWRITE

Resources

Oracle 11g R2 SQL Language Reference

Oracle 11g R2 Database Concepts

Oracle 11g R2 Advanced Replication

Oracle 11g R2 Data Warehousing Guide

SQLines Services

SQLines offers services to migrate Oracle databases and applications to Microsoft SQL Server. For more information, please Contact Us.

You could leave a comment if you were logged in.