Direct JOIN syntax in UPDATE and DELETE (covered in February 2025) is cleaner to write, but does it perform better? The answer depends on the query, and understanding the execution plan differences helps you make the right choice.
Checking execution plans for both approaches:
-- Approach 1: Correlated subquery (classic)
EXPLAIN PLAN FOR
UPDATE employees e
SET e.department_name = (SELECT d.department_name FROM departments d WHERE d.id = e.dept_id)
WHERE EXISTS (SELECT 1 FROM departments d WHERE d.id = e.dept_id AND d.active = 1);
-- Approach 2: Direct JOIN (23ai)
EXPLAIN PLAN FOR
UPDATE employees e
JOIN departments d ON d.id = e.dept_id AND d.active = 1
SET e.department_name = d.department_name;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);What you’ll typically see:
The optimizer often generates the same or equivalent plan for both syntaxes. The direct JOIN form gives the optimizer clearer join semantics, which can lead to better cardinality estimates and more accurate statistics usage.
When direct JOIN is measurably better:
- When the join column has a usable index and the correlated subquery form was defeating the index due to function wrapping
- When the number of rows to update is a small fraction of the target table (the JOIN can use NESTED LOOPS more efficiently)
- When Oracle was previously choosing FILTER operations (which can be slow) for correlated EXISTS subqueries
When they’re equivalent:
For well-written correlated subqueries with proper indexes, the optimizer typically produces identical plans. The direct JOIN benefit is primarily in readability and maintainability, not necessarily raw performance.
General recommendation: Use direct JOIN for clarity. If you observe a performance regression (unlikely but possible on older statistics), compare plans and potentially hint the join type.
