call us toll-free +1 855 855 3600
 
Home > Blog > Oracle to SQL Server migration with SSMA: How to convert materialized view with float type

Oracle to SQL Server migration with SSMA: How to convert materialized view with float type

Posted by | On | In Database Migration, Microsoft SQL Server, Microsoft SQL Server Migration Assistant (SSMA), Operational Data Management, Oracle Database | Tags:
 

SQL Server has several different ways of implementing the same behavior as an Oracle materialized view. SSMA cannot understand the original optimization reason for why the application needed the materialized view, so SSMA flags certain conditions as an error. This blog post discusses error O2SS0522 where the Oracle materialized view includes FLOAT columns, WHERE or GROUP BY clauses, subqueries, UNION, MINUS, INTERSECT, etc.

Currently SQL Server Migration Assistant (SSMA) for Oracle converts materialized views to SQL Server indexed views. While converting a materialized view, SSMA creates necessary unique clustered index on the view in SQL Server. Also, SSMA adds WITH SCHEMABINDING option to the CREATE VIEW statement. So, if the source Oracle’s materialized view contains elements that are prohibited in the SELECT statement of the indexed view, the conversion will fail with an error.

Let us try to convert materialized view with a float type column. SSMA will generate the following error message: «O2SS0522: Materialized view with float type can’t be converted (restriction)». Below we will provide some useful workarounds and show how you can successfully avoid this error and convert Oracle’s source code.

SSMA for Oracle converting materialized view with float type

Possible Remedies

Consider the following 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;

This SQL code describes the Oracle’s materialized view with REFRESH FORCE ON DEMAND option. This allows to improve performance of aggregated queries. We will use the In-Memory tables in the SQL Server target database to ensure that the converted code should provide even better the performance than the original solution.

To solve the O2SS0522 error, you should perform the following actions:

  • create a memory-optimized data filegroup and add a container to the filegroup;
  • create a memory-optimized table in SQL Server with the same structure as Oracle materialized view and index;
  • create procedure that will delete obsolete rows from this table and insert refreshed data in it;
  • create a SQL Server job to execute this procedure on demand.

Conversion result

Hence, to work with the filegroups you can use the following SQL Server code.

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

The updated code of the memory-optimized table in SQL Server will look as follows.

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

And the SQL Server procedure code will look as follows.

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

Do you have other questions or concerns about SSMA or Oracle to SQL Server migration issues? Make sure to check out our new Jumpstart for SSMA offer!

Related posts

Be sure to check the regularly updated table of contents for our series of blog posts on typical SSMA conversion errors.

Source of technical information: Oksana Eremenko, Technical Lead at DB Best.

 
       
 

Leave a Reply

  • CAPTCHA Image
    Play CAPTCHA Audio
    Reload Image