How to convert complex Oracle sqls in the Incorta way

[amit_kothari]

Introduction

This article elaborates the steps to convert complex Oracle SQLS or views to Incorta.

What you should know before reading this article

We recommend that you be familiar with these Incorta concepts before exploring this topic further 

• Designing Spark MVs

Applies to

These concepts apply to all Incorta versions.

Let’s Go

When dealing with Oracle implementations, complex SQL queries or views can create performance bottlenecks. These queries often suffer from slow execution times and make incremental data extraction difficult. One effective approach to overcoming these challenges is converting complex Oracle SQL queries into Materialized Views (MV) using Spark SQL.

This article will guide you through the process of converting Oracle SQL queries into Spark-based MVs while optimizing performance and maintaining data integrity.

Why Convert Oracle Views to Spark MVs?

1. Performance Optimization – Complex queries slow down ingestion and analysis.
2. Incremental Extracts – MVs allow easier incremental data extraction.
3. Better Scalability – Spark MVs reduce the strain on the database and improve query execution time.
4. ANSI SQL Compatibility – Converting Oracle SQL to Spark SQL ensures better compatibility across different systems.

Steps to Convert Complex Oracle SQL to Incorta Materialized Views

1. Table Identification

• Identify all tables used in the Oracle query.
• Ensure that these tables exist in the Incorta environment.
• If necessary, create missing tables in Incorta or modify existing tables to include additional required columns.
• Replace schema names in the converted SQL accordingly.

2. SQL Conversion Process

• Use ChatGPT or other tools to convert Oracle SQL to Spark SQL (ANSI standard joins and functions).
• Expect about 75-80% accuracy in automated conversion, with manual fixes required.
• Replace Oracle-specific syntax with appropriate Spark SQL equivalents.
• Create a Incorta Materialized View (MV) using the converted SQL.

3. Optimizing Materialized Views in Spark

General SQL Optimizations:

• Move filters from the WHERE clause to JOIN conditions for better query performance.
• Use UNION ALL instead of UNION to avoid unnecessary sorting operations.
• Replace EXISTS or IN subqueries with SEMI JOIN.
• Replace NOT EXISTS or NOT IN subqueries with ANTI JOIN.
• Use GROUP BY instead of DISTINCT when aggregating data.
• Replace ROW_NUM() with monotonically_increasing_id() to generate sequence numbers.

MV Load Optimization:

• Reduce deduplication time by filtering out duplicates in the query itself rather than relying on Incorta’s deduplication mechanisms.
• Instead of writing one large SQL query, break it into multiple DataFrames using PySpark and process them in sequence.
• Apply filters early in the logic to minimize the dataset size before joins.
• Persist formula-based joins in separate DataFrames before merging them into the final MV.
• Use a dedicated Spark server to reduce contention for processing resources.

System Considerations:

• Check available memory and CPU cores on the Spark cluster to optimize query execution.
• Tune Spark configurations for memory management and parallel execution.

4. Oracle Function Conversion

• Identify Oracle-specific functions and adapt them for Spark SQL.
• Convert scalar subqueries into JOIN operations for better performance.
• Use subqueries or additional Incorta tables when necessary.

5. Common Manual Fixes

• Replace TRUNC(Datetime_field) with CAST(Datetime_field AS DATE).
• Convert TO_CHAR(Sysdate, 'dd-mon-rr') to TO_CHAR(current_date, 'mm-yyyy').
• Adjust date formatting functions like TO_DATE(PERIOD_NAME, 'MON-RRRR').
• Replace TO_DATE(SYS_CONTEXT ('XXIN_DISCO_CONTEXT','P_DATE'),'DD-MON-RRRR') with current_date.
• Convert ROUND(col1,2) to ROUND(col1) (Spark does not support decimal rounding in the same way as Oracle).
• Remove SQL parameters like :1, :2 from queries.
• Functions in JOIN clauses are not supported; restructure conditions accordingly.
• Convert PL/SQL function calls in the SELECT clause to standard SQL equivalents.
• Replace USERENV (‘LANG’) with 'US'.
• Use LIMIT 1 instead of ROWNUM = 1 for better compatibility.

Validation and Execution

• Execute the converted MV in Spark.
• Validate the output data against the original Oracle tables to ensure consistency and correctness.
• Monitor performance improvements and adjust query logic as needed.
• Please check the sample Oracle EBS view and converted MV attached to the article

Conclusion

Converting complex Oracle SQL queries to Materialized Views in Spark requires a structured approach, including SQL transformation, system optimizations, and manual fixes. By following the best practices outlined in this guide, you can significantly enhance performance and scalability while ensuring seamless data integration.

By leveraging Spark MVs, organizations can optimize their data pipelines, reduce processing overhead, and improve the efficiency of their analytical workloads.

Related Material

• Best practices