Unlike traditional row-based
storage, The In-Memory Column Store (IM column store) stores
tables and partitions in memory using a columnar format optimized
for rapid scans. This
columnar format is optimized for analytical workloads, allowing for
efficient scanning of specific columns without needing to read entire rows.
In-Memory Optimized Dates
To enhance the performance of
DATE-based queries DATE components (i.e. DAY, MONTH, YEAR) can be extracted and
populated in the IM column store leveraging the In-Memory Expressions
framework. This approach enables faster query
processing on DATE columns, significantly improving the performance of
date-based analytic queries.
For a long time, working with DELETE and MERGE in Oracle
meant dealing with awkward syntax and unnecessary complexity, especially when
joins or feedback from DML operations were involved. Oracle 23ai finally
removes many of those pain points.
In this blog, I will look at three practical enhancements:
join-based deletes using DELETE … FROM, capturing affected rows with DELETE …
RETURNING, and retrieving results directly from MERGE operations using RETURNING.
DELETE … FROM
For a long time, deleting rows based on conditions in
another table was cumbersome in Oracle. You often had to write complicated EXISTS
clauses or nested subqueries, which made the code harder to read and maintain.
With Oracle 23ai, you can now use a direct FROM clause in your DELETE
statement to join the target table with other tables. This aligns the syntax
with how we write SELECT queries or UPDATE ... FROM statements, making the
intent of your SQL clearer.
Example: Remove Employees in the IT Department
Suppose you want to delete all employees who work in the Shipping
department. Previously, you might have had to write a subquery like DELETE FROM
employees WHERE department_id IN (SELECT department_id FROM departments WHERE
department_name = 'IT'). Now, you can do it more cleanly using a join:
Select e.employee_id,e.first_name, e.last_name
FROM employees e, departments d
WHERE e.department_id = d.department_id
AND d.department_name = 'IT';
DELETE FROM employees e
FROM departments d
WHERE e.department_id = d.department_id
AND d.department_name = 'IT';
Commit;
DELETE ... RETURNING
The RETURNING clause is a real game-changer when it comes
to maintaining data integrity. In earlier versions of Oracle, you could use it,
but in 23ai its usefulness really shines, especially during more complex
deletions. Instead of running a separate SELECT to grab the values before you
delete them, RETURNING lets you capture the data at the moment it’s being
deleted. This makes it perfect for logging, archiving, or passing
information back to your application immediately.
Example: Deleting an Employee and Logging Their Info
Suppose you want to remove a specific employee from the
database but also keep a record of their name and final salary for auditing
purposes. With RETURNING, you can do this in one step:
-- Create a demo table
CREATE TABLE employees_demo (
employee_id NUMBER PRIMARY KEY,
first_name VARCHAR2(50),
last_name VARCHAR2(50)
);
-- Insert a test row
INSERT INTO employees_demo VALUES (999, 'AMIR', 'KORDESTANI');
COMMIT;
-- Now delete with RETURNING
SET SERVEROUTPUT ON;
DECLARE
v_name VARCHAR2(100);
v_employee_id employees_demo.employee_id%TYPE;
BEGIN
DELETE FROM employees_demo
WHERE employee_id = 999
RETURNING (first_name || ' ' || last_name), employee_id
INTO v_name, v_employee_id;
DBMS_OUTPUT.PUT_LINE('Deleted Employee: ' || v_name);
DBMS_OUTPUT.PUT_LINE('EMPLOYEE_ID: ' || v_employee_id);
END;
/
MERGE ... RETURNING
The MERGE statement lets you insert and update rows in a
single operation. In earlier versions of Oracle, MERGE could feel like a black
box: you wouldn’t know the final values of the affected rows without running an
additional SELECT.
Oracle 23ai changes that with the RETURNING clause for
MERGE. Now, whether a row is updated or newly inserted, you can immediately
capture the resulting values. This is especially useful for logging, sequences,
or auto-calculated columns.
Example: Upserting Employees and
Capturing Their Final Bonus
Imagine you have a table of employees_demo and
want to either update an existing employee’s bonus or add a new employee if
they don’t exist. At the same time, you want to know the final bonus
immediately. The MERGE … RETURNING statement lets you do this in a single
step, capturing the result directly into a variable and displaying it.
The MERGE statement lets us either update
or insert a row in one atomic operation in the follwoing example:
USING (SELECT 101 AS emp_id, 10000 AS
new_bonus FROM dual) defines the source data. Here, we want
employee 101 to have a bonus of 10000.
ON (e.employee_id = s.emp_id) specifies
how to match rows in the target table with the source.
WHEN MATCHED THEN UPDATE: If
employee 101 already exists (Alice), her bonus_amount is updated to 10000.
WHEN NOT MATCHED THEN INSERT: If
employee 101 didn’t exist, a new row would be inserted with the provided
values.
The RETURNING e.bonus_amount INTO
v_final_bonus clause immediately captures the resulting bonus after the MERGE.
This works whether the row was updated
or inserted, eliminating the need for a separate SELECT to find the
final value.
-- Create a safe demo table for employees
CREATE TABLE employees_demo (
employee_id NUMBER PRIMARY KEY,
first_name VARCHAR2(50),
last_name VARCHAR2(50),
hire_date DATE,
bonus_amount NUMBER
);
-- Insert an initial row
INSERT INTO employees_demo VALUES (101, 'Amir', 'Kordestani', DATE '2025-01-01', 5000);
COMMIT;
SET SERVEROUTPUT ON;
DECLARE
v_final_bonus employees_demo.bonus_amount%TYPE;
BEGIN
MERGE INTO employees_demo e
USING (SELECT 101 AS emp_id, 10000 AS new_bonus FROM dual) s
ON (e.employee_id = s.emp_id)
WHEN MATCHED THEN
UPDATE SET e.bonus_amount = s.new_bonus
WHEN NOT MATCHED THEN
INSERT (employee_id, first_name, last_name, hire_date, bonus_amount)
VALUES (s.emp_id, 'New', 'Hire', SYSDATE, s.new_bonus)
RETURNING e.bonus_amount INTO v_final_bonus;
-- Show the result
DBMS_OUTPUT.PUT_LINE('Final bonus for employee ' || 101 || ': ' || v_final_bonus);
END;
/
Conclusion
Oracle 23ai is a real step forward for day-to-day SQL
development. Enhancements like join-based deletes and RETURNING support for
both DELETE and MERGE finally remove some long-standing pain points that many
of us have worked around for years.
These improvements aren’t just about writing less code, they
make SQL easier to read, easier to reason about, and safer to maintain. If
you’re moving to Oracle 23ai or 26ai, it’s worth taking the time to revisit
older DELETE and MERGE statements and refactor them using this newer syntax.
Oracle Database 23ai brings meaningful
improvements to the UPDATE statement, making everyday data changes simpler and
more intuitive. Long-standing limitations—such as complex join updates and
extra queries to retrieve updated values—are now addressed with cleaner, more
expressive SQL. Features like UPDATE … FROM enable direct join-based updates,
while UPDATE … RETURNING allows immediate access to modified data. Native
BOOLEAN support and the DEFAULT ON NULL clause further reduce workarounds and
conditional logic. Together, these enhancements help developers write clearer,
safer, and more maintainable UPDATE statements that better reflect real-world
data operations.
1. UPDATE … FROM (Direct Join
Support)
The “Finally!” feature for cleaner
SQL
For years, updating a table based on another table’s data required tricky
correlated subqueries or using MERGE. With Oracle 23ai, you can now join
tables directly in an UPDATE statement using a FROM clause. This brings Oracle
closer to PostgreSQL and SQL Server syntax, making the code far easier to read
and maintain.
Example: Give a 10% raise to all employees in the Executive
department.
Here, the UPDATE … FROM syntax allows you to
reference the departments table directly. Oracle automatically joins the tables
based on the condition in the WHERE clause. This eliminates the need for
subqueries and ensures that only employees in the 'Executive' department get
their salaries updated.
UPDATE employees e
SET e.salary = e.salary * 1.10
FROM departments d
WHERE e.department_id = d.department_id
AND d.department_name = 'Executive';
commit;
2. UPDATE … RETURNING Old and New
Values
Previously, the RETURNING clause
in Oracle only allowed you to capture the new values after an
update. If you wanted the previous state, you had to perform a separate SELECT,
which added complexity and risk of inconsistencies. With Oracle 23ai, the
introduction of the OLD and NEW keywords allows you to capture both the
previous and updated values in a single, atomic operation. This makes it much
easier to build audit logs, real-time notifications, or application logic that
reacts to changes in data.
Example: Capture a salary change for a specific employee.
In this example, Oracle updates
the employee’s salary by 500 and simultaneously stores the previous salary
in :v_old_sal and the new salary in :v_new_sal. This eliminates
the need for a separate query to fetch the old value.
SET SERVEROUTPUT ON;
DECLARE
v_old_sal employees.salary%TYPE;
v_new_sal employees.salary%TYPE;
BEGIN
-- Update and capture old and new salary
UPDATE employees
SET salary = salary + 500
WHERE employee_id = 101
RETURNING OLD salary, NEW salary
INTO v_old_sal, v_new_sal;
-- Print the results
DBMS_OUTPUT.PUT_LINE('Old Salary: ' || v_old_sal);
DBMS_OUTPUT.PUT_LINE('New Salary: ' || v_new_sal);
END;
/
3. Native BOOLEAN Support in
UPDATE
For decades, Oracle developers had
to simulate boolean values using CHAR(1) or NUMBER(1) columns, often combined
with check constraints to enforce TRUE/FALSE logic. Oracle 23ai introduces a
native BOOLEAN data type, allowing you to use TRUE, FALSE, and NULL
directly in your UPDATE statements. This makes your schema more intuitive,
simplifies logic, and improves code readability.
Example: Flagging employees who joined before 2010 as
"Legacy" staff.
In this example, the new is_legacy
BOOLEAN column allows you to mark employees hired before 2013 directly with TRUE.
There’s no need for CHAR or NUMBER workarounds, and querying is straightforward
using TRUE/FALSE conditions.
-- Add a boolean column
ALTER TABLE employees ADD is_legacy BOOLEAN;
-- Update the flag for legacy employees
UPDATE employees
SET is_legacy = TRUE
WHERE hire_date < DATE '2013-01-01';
-- Query using the boolean column
SELECT last_name
FROM employees
WHERE is_legacy = TRUE;
4. DEFAULT ON NULL for UPDATE
The DEFAULT ON NULL clause, originally available for INSERT statements, and now It has been extended to UPDATE in Oracle 23ai. This means that if an application
attempts to set a column to NULL, Oracle will automatically replace it with the
column’s defined default value. This feature helps maintain data integrity
without the need for complex triggers or additional checks, ensuring that
critical columns never end up with invalid or missing data.
Example: Prevent an employee’s bonus from ever becoming NULL.
In this example, the commission_pct column
is protected against accidental NULL updates. Even if an application sends
a NULL value, Oracle automatically applies the default (0 in
this case).
-- Enable DEFAULT ON NULL for the column
ALTER TABLE employees
MODIFY commission_pct DEFAULT ON NULL for Insert and update 0;
-- Attempt to set commission to NULL
UPDATE employees SET commission_pct = NULL WHERE employee_id = 999;
commit;
-- You will see 0 instead of NULL!
SELECT employee_id, first_name,last_name, commission_pct FROM employees WHERE employee_id = 999;
Conclusion
The UPDATE enhancements in Oracle 23ai
significantly modernize one of the most commonly used DML operations. By
simplifying join-based updates, capturing updated values in a single step,
supporting BOOLEAN columns, and handling NULL assignments more intelligently,
Oracle removes much of the complexity that developers have worked around for
years. These features improve readability, reduce boilerplate code, and lower
the risk of errors in data modification logic. When combined, they make UPDATE
statements more expressive and aligned with modern SQL practices, reinforcing
Oracle 23ai’s focus on developer productivity and cleaner database design.
