A materialized view is an important property of Oracle. SQL Server recreates the same behavior as this materialized view in several ways. Unfortunately, SSMA may have issues with converting this object to SQL Server. It flags certain conditions as an error. In this blog post, we will overview the error O2SS0522. It goes about cases in which the Oracle materialized view includes FLOAT columns, subqueries, GROUP BY or WHERE clauses, MINUS, INTERSECT, UNION, etc.

By default, SQL Server Migration Assistant (SSMA) converts Oracle materialized views to SQL Server indexed views. During this conversion, SSMA creates a clustered index on the SQL Server materialized view. Besides, SSMA adds the WITH SCHEMABINDING option to the CREATE VIEW statement. As a result, conversion of the Oracle materialized view to SQL fails if the original view contains elements that are not available in the SELECT statement of the indexed view.

Now, let’s see what happens when we try to convert the view mentioned above with a float-type column. If we do so, we will receive the following error message from SSMA: «O2SS0522: Materialized view with float type can’t be converted (restriction)».

Read the next chapter to find out about efficient solutions which will help you to avoid this error and successfully convert Oracle source code with the materialized views to SQL Server 2019.

Possible solutions

Let’s take a look at this example:

1
2
3
4
5
6
7
8
9
10
11
CREATE
MATERIALIZED VIEW MV_REFRESH_ON_DEMAND
REFRESH FORCE ON DEMAND
AS
SELECT EMP.EMP_ID, DEPT.DEPT_ID, DEPT.DEPT_NAME, SUM(EMP.SAL)
FROM TEST_ORACLE_MSSQL.T_FOR_MV_EMP EMP, TEST_ORACLE_MSSQL.T_FOR_MV_DEPT DEPT
WHERE EMP.DEPT_ID = DEPT.DEPT_ID
GROUP BY
EMP.EMP_ID,
DEPT.DEPT_ID,
DEPT.DEPT_NAME;

In this case, SQL code describes Oracle materialized view with the REFRESH FORCE ON DEMAND option. This step ensures the improvement of aggregate queries’ performance.  In-Memory tables of the SQL Server target database will help to ensure even better performance with the converted code.

Meanwhile, if you want to solve the O2SS0522 error, follow these steps:

  • Create a specific memory-optimized data filegroup and add a file to it;
  • Create a memory-optimized SQL Server table that has the same structure as Oracle materialized view and index;
  • Create a procedure that will automatically delete obsolete rows in the aforementioned table and insert refreshed data into it;
  • Create a SQL Server job that will execute this procedure on demand.

After the conversion, you will be able to use the following SQL Server code while working with the filegroups.

1
2
3
4
5
6
7
ALTER DATABASE INMEM ADD FILEGROUP INMEM_MOD CONTAINS MEMORY_OPTIMIZED_DATA
GO

ALTER DATABASE INMEM ADD FILE (NAME='INMEM_MOD',
FILENAME='C:Program FilesMS SQL ServerMSSQL13.MSSQLSERVERMSSQLDATAINMEM_MOD')
TO FILEGROUP INMEM_MOD
GO

Here is how the updated code of the memory-optimized SQL Server table should look like:

1
2
3
4
5
6
7
8
9
10
CREATE TABLE dbo.MV_REFRESH_ON_DEMAND
([Id] uniqueidentifier NOT NULL PRIMARY KEY NONCLUSTERED DEFAULT (NEWID()),
EMP_ID FLOAT(53),
DEPT_ID FLOAT(53),
DEPT_NAME VARCHAR(50),
SUM_SAL FLOAT(53))
WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_ONLY)
-- NOTE: You don't need SCHEMA_AND_DATA durability since you are routinely
-- refreshing the data based on the stored procedure below.
GO

Meanwhile here is the SQL Server procedure code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
ALTER PROCEDURE dbo.PROC_MV_REFRESH_ON_DEMAND
AS
BEGIN
DELETE FROM dbo.MV_REFRESH_ON_DEMAND -- You can also use TRUNCATE command
INSERT INTO dbo.MV_REFRESH_ON_DEMAND (EMP_ID, DEPT_ID, DEPT_NAME, SUM_SAL)
SELECT EMP.EMP_ID, DEPT.DEPT_ID, DEPT.DEPT_NAME, SUM(EMP.SAL)
FROM dbo.T_FOR_MV_EMP EMP, dbo.T_FOR_MV_DEPT DEPT
WHERE EMP.DEPT_ID = DEPT.DEPT_ID
GROUP BY EMP.EMP_ID, DEPT.DEPT_ID, DEPT.DEPT_NAME;
END
GO
-- You will want to schedule the execution of the stored procedure based on
-- your business needs for current data.
EXEC dbo.PROC_MV_REFRESH_ON_DEMAND
GO

Final thoughts

Did you find this material useful? You may still have more questions on how to efficiently use SSMA for Oracle to SQL Server database migration. Please check out our Jumpstart for the SSMA offer and do not hesitate to contact ABCloudz if you have any questions. We will provide answers and help you with the database migration. Also, make sure to check our table of contents for the blog post series on common SSMA errors pertaining to Oracle to SQL Server migration.

Ready to start the conversation?