Migrating Oracle Materialized View to SQL Server

  • [アーティクル]

By Gowri Shanker and Welly Lee.

This blog post discusses approaches to migrate Oracle Materialized View to SQL Server. We compare Materialized View with SQL Server Indexed View, discuss scenarios supported by Indexed View and provide suggestions you can consider for the non-supported scenarios.

Converting Materialized View to SQL Server

Oracle supports database object called Materialized View, which unlike a normal view, stores the result set of the query defining the view. The tables/views referred in the query are collectively called master tables. A materialized view can be described as a replica of the master table(s) from a single point in time. The data in the materialized view can be updated from the master tables through a process called Refresh. Oracle provides different refresh modes and interval options like automatic periodic refresh and on demand refresh for this purpose.

Common usage scenarios for materialized view include:

  1. Replicating data in a replication environment
  2. Caching result set of expensive queries in a data warehouse environment
  3. Tuning complex and long running queries to improve execution/response time.

A materialized view can be read-only, updateable and writable. Based on the environment they are used in, Oracle offers different types of materialized views including Primary Key materialized view (including sub-query materialized view), Object materialized view, ROWID materialized view, Complex Materialized view (like materialized join view and materialized aggregate view)etc.

SQL Server has Indexed view that can provide similar functionalities as a Materialized view. SQL Server supports creating an index on a view. Creating an index on a view, results in storage of logical data from the view into physical index files thereby materializing the query results.

The following is an example for creating indexed view:

Oracle SQL Server

CREATE TABLE MAT_VIEW_MASTER_TABLE ( COL1 NUMBER PRIMARY KEY, COL2 VARCHAR2(25) );

CREATE TABLE dbo.MAT_VIEW_MASTER_TABLE ( COL1 INT PRIMARY KEY, COL2 VARCHAR(25) );

CREATE MATERIALIZED VIEW MAT_VIEW_1   AS SELECT COL1, COL2 FROM MAT_VIEW_MASTER_TABLE;

CREATE VIEW dbo.MAT_VIEW_1 WITH SCHEMABINDING AS SELECT COL1, COL2 FROM dbo.MAT_VIEW_MASTER_TABLE GO

CREATE UNIQUE CLUSTERED INDEX MAT_VIEW_INDX_1 ON dbo.MAT_VIEW_1 (COL1) GO

One key advantage of using Index View to convert Materialized view is that indexed views are dynamically refreshed by SQL Server itself whenever the base tables are updated so you do not need to define any refresh modes/periods. You should consider using Indexed View to convert Oracle Materialized View to SQL Server, however, please note the following requirements for Indexed View during conversion:

Referencing underlying database object(s)

Unlike Materialized View which can refer to any base table, view or another materialized view, SQL Indexed view can only refer to base tables. Moreover, Index view must be created with schema binding option to the underlying base table and the base table must be in the same database as the indexed view.

Oracle SQL Server

CREATE VIEW VIEW_2 AS SELECT COL1, COL2 FROM MAT_VIEW_MASTER_TABLE WHERE COL1 >= 1 AND COL1 <= 10;

  

CREATE MATERIALIZED VIEW MAT_VIEW_2 AS SELECT COL1, COL2 FROM  VIEW_2 WHERE COL1 >=5 AND COL1 <= 8;

CREATE VIEW dbo.MAT_VIEW_2 WITH SCHEMABINDING AS /* Creating indexed view on another view is not supported. Instead, convert materialized view into an indexed view by referring TO the base table directly. */ SELECT COL1, COL2 FROM dbo.MAT_VIEW_MASTER_TABLE WHERE COL1 >= 5 AND COL1 <= 8 GO

CREATE UNIQUE CLUSTERED INDEX MAT_VIEW_INDX_2 ON dbo.MAT_VIEW_2 (COL1) GO

Column with type of text, ntext, image can’t be referenced in the view.

Query Definition

The * or table_name.* syntax to specify columns is not allowed in SELECT statement of the view. Column names must be explicitly stated.

Oracle SQL Server

CREATE MATERIALIZED VIEW MAT_VIEW_3 AS SELECT * FROM MAT_VIEW_MASTER_TABLE WHERE COL1 >=5 AND COL1 <= 8;

CREATE VIEW dbo.MAT_VIEW_3 WITH SCHEMABINDING AS /* Indexed View does not support query statement with SELECT * Expand the SELECT * to refer to the column name of the table */ SELECT COL1, COL2 FROM dbo.MAT_VIEW_MASTER_TABLE WHERE COL1 >= 5 AND COL1 <= 8 GO

CREATE UNIQUE CLUSTERED INDEX MAT_VIEW_INDX_3 ON dbo.MAT_VIEW_3 (COL1) GO

The SELECT statement cannot contain:

  • Common Table Expression (CTE)
  • UNION, EXCEPT or INTERSECT operators
  • Sub-queries
  • Outer or self joins
  • TOP clause
  • ORDER BY clause
  • DISTINCT keyword
  • COUNT (COUNT_BIG(*) is allowed.)
  • AVG, MAX, MIN, STDEV, STDEVP, VAR, or VARP aggregate functions

If GROUP BY is specified, the select list must contain a COUNT_BIG(*) expression, and the view definition cannot specify HAVING, ROLLUP, CUBE, or GROUPING SETS

Oracle SQL Server

CREATE MATERIALIZED VIEW MAT_VIEW_4 AS SELECT SUM(COL1) AS TOTAL, COL2 AS COL2 FROM MAT_VIEW_MASTER_TABLE GROUP BY COL2;

CREATE VIEW dbo.MAT_VIEW_4 WITH SCHEMABINDING AS /* SELECT statement with GROUP BY must include COUNT_BIG(*) expression */ SELECT SUM(COL1) AS TOTAL, COL2 AS COL2, COUNT_BIG(*) AS CBIG FROM dbo.MAT_VIEW_MASTER_TABLE GROUP BY COL2 GO

CREATE UNIQUE CLUSTERED INDEX MAT_VIEW_INDX_4 ON dbo.MAT_VIEW_4 (COL2) GO

Index Creation

In order to create an indexed view, a unique clustered must first be created. Additional non-clustered index(es) can be created. If the index is dropped, the stored result set is removed and the view is processed as a normal view.

If the SELECT statement in the view definition specifies a GROUP BY clause, the key of the unique clustered index can reference only columns specified in the GROUP BY clause.

Oracle SQL Server

CREATE MATERIALIZED VIEW MAT_VIEW_5 AS SELECT SUM(COL1) AS TOTAL, COL2 AS COL2 FROM MAT_VIEW_MASTER_TABLE GROUP BY COL2;

CREATE VIEW dbo.MAT_VIEW_5 WITH SCHEMABINDING AS SELECT SUM(COL1) AS TOTAL, COL2 AS COL2, COUNT_BIG(*) AS CBIG FROM dbo.MAT_VIEW_MASTER_TABLE GROUP BY COL2 GO

/* Index is created on the column referred in the GROUP BY */ CREATE UNIQUE CLUSTERED INDEX MAT_VIEW_INDX_5 ON dbo.MAT_VIEW_5 (COL2) GO

 

Special Case: NEVER REFRESH Materialized View

There may be cases where Oracle Materialized view could be setup with NEVER REFRESH clause. This prevents the view from being refreshed by any refresh mechanisms. Such cases can be converted to standalone tables in SQL Server.

Oracle SQL Server

CREATE MATERIALIZED VIEW MAT_VIEW_6 NEVER REFRESH AS SELECT COL1, COL2 FROM MAT_VIEW_MASTER_TABLE;

SELECT COL1, COL2 INTO MAT_VIEW_6 FROM MAT_VIEW_MASTER_TABLE

Concluding Thought

Materialized View should be converted to Index View during migration to SQL Server. In addition to providing dynamic data refresh advantage, creating an indexed view can also improve query against the underlying table(s). SQL Server query processor can refer to the indexed view to determine the query plan even if the query does not explicitly refer to the view. In the case where the Materialized View can’t be converted to Indexed View, you should consider simplify the query, else create a standalone table and create a stored procedure to synchronize values in the table

One last note, Oracle creates an internal table with the same name as the materialized view for storing the snapshot. SSMA for Oracle actually loads this and converts it as a standalone table. If the approach of converting materialized view to standalone table is not the one followed, then you need to exclude this internal table when converting Oracle schema using SSMA

Reference

SQL Server Books Online: Creating Indexed View

Comments

  • Anonymous
    April 12, 2012
    Why SLQ Server doesn't allow creating indexed views with subqueries? This is a serious limitation during migration from Oracle.

  • Anonymous
    June 01, 2016
    Restrictions on Indexed Views are so severe that most Oracle materialized views cannot be converted to a SQL Server Index View.