Tags: code conversion, database conversion errors
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.
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
- How to convert foreign keys with different types of columns and referenced columns
- How to convert cursor or cursor variable as a function or procedure call parameter
- How to convert unsupported table expressions
- How to convert Unparsed SQL — Pivot Operator
- How to convert interval expressions
- How to convert interval literals
- How to convert database links
- How to convert aggregate functions referring to remote table
- How to convert unsupported SQL clause
- How to convert virtual columns
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.
