Understanding Oracle SQL’s Union Issue with Full Outer Join

Introduction

As a database administrator or developer working with Oracle Database, it’s not uncommon to encounter issues when using the UNION operator in combination with full outer joins. In this article, we’ll delve into the root cause of this problem and explore a potential workaround.

Background

In Oracle Database 11g Release 1, a native support for hash full outer joins was introduced to improve performance and reduce the need for the pre-11gR1 strategy of converting ANSI full outer joins to UNION ALL queries. However, even in newer versions of Oracle, certain scenarios can lead to this conversion occurring.

The Problem

Our question comes from a user who has two views: “view1” and “view2”. “view1” uses a complex query with a full outer join, while “view2” simply selects all columns from “view1”. When the user runs “view2”, they encounter an error (ORA-01790) indicating that expressions must have the same datatype as corresponding expressions.

The issue arises because the UNION operator has different datatypes for each branch of the join, causing Oracle to fail.

Workaround

To resolve this issue, the user applied a workaround in “view1” by wrapping the column with TO_CHAR and then converting it to TO_NUMBER later (for calculation purposes). This modification seems to bypass the error.

However, we need to understand why this workaround works and how it relates to the native support for hash full outer joins introduced in Oracle 11gR1.

The Answer

According to the Oracle blog, when using a native full outer join, Oracle reverts to the pre-11gR1 strategy if certain conditions are met. This can lead to UNION instead of outer join being used.

To resolve this issue, we need to understand that different datatypes for each branch of the join can cause the UNION operator to fail. In our case, the columns in “view1” have different datatypes, leading Oracle to use UNION ALL instead of a full outer join.

The workaround applied in “view1” uses TO_CHAR and TO_NUMBER to resolve this issue by converting the column to a common datatype.

Why Does This Work?

When using TO_CHAR and TO_NUMBER, we’re effectively casting the data to a common datatype. In our case, this allows us to bypass the error caused by different datatypes for each branch of the join.

By applying this modification in “view1”, we’re essentially telling Oracle that we want to resolve the datatype issue using these conversions. This resolves the UNION-related problem and allows the query to run successfully.

Conclusion

In conclusion, the error ORA-01790 occurs when there are different datatype groups using an operator such as UNION, UNPIVOT, INTERSECT, or EXCEPT. The native support for hash full outer joins introduced in Oracle 11gR1 can lead to this conversion occurring.

By understanding the root cause of this problem and applying a workaround like TO_CHAR and TO_NUMBER, we can resolve the issue and ensure our queries run successfully.

Additional Considerations

When working with complex queries involving full outer joins, it’s essential to analyze the explain plans to identify potential issues. By reviewing these plans, we can detect if Oracle is reverting to the pre-11gR1 strategy due to certain conditions.

Additionally, when encountering errors like ORA-01790, it’s crucial to review the datatypes of each column involved and ensure they match across all branches of the join.

Last modified on 2023-09-22