Mastering SQL Joins: Boost Performance When Migrating from Oracle to Open‑Source Databases

Why Joins Matter in Database Migration

Many developers migrating from Oracle to domestic or open‑source databases report dramatic performance loss, usually because the optimizer chooses a different execution plan for join operations. While single‑table queries may only be slightly slower, a poorly chosen join plan can turn a query that runs in seconds on Oracle into one that never finishes.

Test Environment

The examples below use two test tables created in a PostgreSQL 12.3 instance.

Inner Join

Typical syntax:

SELECT count(*) FROM join1 j1 INNER JOIN join2 j2 ON j1.id = j2.id;

The equivalent comma‑style syntax is: SELECT count(*) FROM join1 j1, join2 j2 WHERE j1.id = j2.id; For large tables the optimizer prefers a HASH JOIN plan, which can be seen in the execution plan image:

If the join condition is changed to inequality, the plan switches to a slower NESTED LOOP :

SELECT count(*) FROM join1 j1, join2 j2 WHERE j1.id <> j2.id;

Execution time may jump from 8 ms to nearly 40 seconds. In such cases, rewriting the query with NOT IN or NOT EXISTS can restore performance.

Left Outer Join

Returns all rows from the left table plus matching rows from the right:

SELECT count(*) FROM join1 j1 LEFT JOIN join2 j2 ON j1.id = j2.id;

Sometimes the optimizer rewrites this as a right‑join with the right table as the driver, which can be more efficient, as shown in the plan image:

Right Outer Join

Symmetric to left join:

SELECT count(*) FROM join2 j1 RIGHT JOIN join2 j2 ON j1.id = j2.id;

The optimizer may again choose the opposite side as the driver if it yields a better plan.

Semi Join

A semi join returns rows from the left table that have at least one matching row in the right table, without duplicating the right side:

SELECT count(*) FROM join1 j1 WHERE EXISTS (SELECT id FROM join2 j2 WHERE j1.id = j2.id);

Anti Join

An anti join returns rows from the left table that have no matching rows in the right table:

SELECT count(*) FROM join1 j1 WHERE NOT EXISTS (SELECT id FROM join2 j2 WHERE j1.id = j2.id);

Common Pitfall: OR Conditions

A frequently problematic query after migration is:

SELECT j1.* FROM join1 j1, join2 j2 WHERE j1.id = j2.id OR j1.id = 100;

Oracle handles the OR efficiently, but many open‑source databases fall back to a nested‑loop plan, causing severe slowdown. A typical remedy is to rewrite the query using UNION:

SELECT j1.* FROM join1 j1, join2 j2 WHERE j1.id = j2.id
UNION
SELECT j1.* FROM join1 j1 WHERE j1.id = 100;

Conclusion

Understanding the different join types and how the optimizer selects execution plans is essential for successful Oracle‑to‑open‑source database migrations. By choosing the appropriate join syntax, leveraging HASH JOINs, and rewriting queries with NOT EXISTS or UNION when necessary, you can avoid the most common performance traps and achieve smooth migration.