Oracle Database 23c: New feature - Direct Joins for UPDATE and DELETE Statements

December 16, 2023 | 5 minute read
Adrian Castillo Mendoza
Product Manager
Gerald Venzl
Senior Director
Text Size 100%:

Oracle Database 23c introduces a host of powerful features aimed at enhancing database performance and query optimization. Among these innovations, Direct Join stands out as an efficient mechanism to streamline update and delete operations involving multiple related tables.

23c now allows you to use direct joins to other tables in UPDATE and DELETE statements in the FROM clause, in this article we will use SQL code examples to HR schema, so you can practice and learn it.

The main benefit is to make the coding of these Direct Joins simpler, using less code and more readable for SQL developers.

Scenario 1: Updating Salaries based on Department and City

Consider the scenario where we need to update the salaries of employees working in the Marketing department in the city of Toronto by increasing their salaries by 10%.

Prior to Oracle Database 23c, we might have used a query similar to this:

In earlier 23c Oracle versions, we might have used a query similar to this

 

 

 

 

 

 

 

 

 

result SQL 1

 

 

 

Once we identify the records that need to be updated, we proceed to design our UPDATE statement to modify only those records. Therefore, in Oracle versions prior to 23c, the filter used in the UPDATE statement is quite similar to the filter used in the SELECT statement.

Prior to Oracle Database 23c, the common approach to formulate the UPDATE statement involved employing an inner query within the WHERE clause.

SQL2-earlier 23c-direct-Join

 

 

 

 

 

 

 

 

 

 

result SQL 2

 

 

 

However, in 23c, we can harness the power of Direct Join to optimize this update operation:

SQL 3 Direct Join

 

 

 

 

 

 

 

result SQL 2version 23c

 

 

 

By utilizing Direct Join, we simplify the query, eliminate subqueries, and improve overall performance.

Scenario 2: Delete employee Neena's job history where she worked as AC_ACCOUNT

Suppose we need to delete employee Neena's job history where she worked as AC_ACCOUNT.

Prior to Oracle Database 23c, we might have used a query similar to this:

SQL 4 Direct Join

 

 

 

 

 

 

 

 

Result SQL 4

 

 

Once we identify the records that need to be deleted, we proceed to design our DELETE statement to remove only those records. Therefore, in prior to 23c, the filter used in the DELETE statement is quite similar to the filter used in the SELECT statement.

Prior to Oracle Database 23c, the common approach to formulating the DELETE statement involved employing a subquery or inner query within the WHERE clause.

SQL 5 Direct Join

 

 

 

 

 

 

 

result SQL 5

 

 

However, 23c, we can harness the power of Direct Join to optimize this DELETE operation:

version 23c

 

 

 

SQL 6 Direct Join

 

 

 

 

 

result SQL 5

 

 

Read more details about syntax UPDATE and DELETE statements and Direct Joins.

Direct Joins for UPDATE and DELETE Statements

UPDATE Syntax

DELETE Syntax

Acknowledgments

Authors - Adrian Castillo Mendoza, Gerald Venzl

Last Updated By/Date - Adrian Castillo Mendoza, September 2023.

 

Adrian Castillo Mendoza

Product Manager

More than 15 years of experience in digital transformation solutions in different industries, more than 15 years of experience in Oracle Databases. 

Gerald Venzl

Senior Director

Gerald Venzl is a Senior Director of Product Manager for Oracle. During his career, Gerald has worked as a Developer, DBA, Performance Tuner, Software Architect, Consultant and Enterprise Architect prior to his current role, while still being active as a developer in his free time and on open source projects. This allowed Gerald to live several different lives in the IT sector, providing him with a solid understanding of the concerns in these individual areas while gaining a holistic view overall. Gerald focuses on advocating how to build systems that provide flexibility yet still meet the needs of the users.


Previous Post

Backup Enhancements on Autonomous Database on Dedicated Infrastructure and Exadata Cloud@Customer

Jeffrey Cowen | 4 min read

Next Post


Oracle Graph Learning Path

Rahul Tasker | 5 min read