Oracle has taken a major leap forward with Oracle Database 23ai, introducing hundreds of new features designed to simplify development, improve performance, and align more closely with modern SQL standards. While the full feature list is extensive, this article walks you through some of the most impactful and developer-friendly enhancements you should start using right away.
Let’s break them down in a practical, easy-to-understand way.
1. SELECT Without FROM Clause – Goodbye DUAL
One of the most noticeable improvements in Oracle 23ai is the ability to run SELECT statements without needing the DUAL table.
Before (Old Versions)
SELECT SYSDATE FROM dual;
SELECT 5280 * 1.5 FROM dual;Now (Oracle 23ai)
SELECT SYSDATE;
SELECT 5280 * 1.5;Why This Matters
- Cleaner and more readable SQL
- Less unnecessary boilerplate
- Aligns Oracle with other modern databases
This feature is especially useful for:
- Quick calculations
- Function calls
- Testing expressions
2. GROUP BY Column Alias or Position
Grouping data just became more flexible.
Using Column Alias
Previously, Oracle required full column names in GROUP BY. Now you can use aliases:
SELECT department_id AS dept, AVG(salary)
FROM employees
GROUP BY dept;Using Column Position
You can also group by the column’s position in the SELECT list:
ALTER SESSION SET group_by_position_enabled = TRUE;
SELECT department_id, AVG(salary)
FROM employees
GROUP BY 1;Benefits
- Shorter and cleaner queries
- Easier maintenance when working with long column names
- Matches behavior in other SQL platforms
3. Table Value Constructor (TVC)
This is a powerful addition that allows you to define inline datasets.
Example with SELECT
SELECT e.first_name, e.last_name
FROM employees e
JOIN (VALUES (100, 200), (101, 201)) v(emp_id, mgr_id)
ON e.employee_id = v.emp_id;Why It’s Useful
- Create temporary datasets without tables
- Simplify joins with static data
- Improve readability in test queries
4. Multi-Value INSERT – Insert Multiple Rows in One Go
In older versions, inserting multiple rows required multiple statements.
Before
INSERT INTO departments VALUES (1, 'HR');
INSERT INTO departments VALUES (2, 'Finance');
INSERT INTO departments VALUES (3, 'IT');Now (Oracle 23ai)
INSERT INTO departments (id, name)
VALUES
(1, 'HR'),
(2, 'Finance'),
(3, 'IT');Benefits
- Faster inserts
- Reduced network overhead
- Cleaner scripts
This is especially useful for:
- Batch data loading
- Initial setup scripts
- Testing environments
5. Direct Joins for UPDATE and DELETE
One of the most developer-requested features is finally here!
UPDATE with JOIN
UPDATE employees e
SET salary = j.max_salary
FROM jobs j
WHERE e.job_id = j.job_id;DELETE with JOIN
DELETE FROM job_history jh
USING employees e
WHERE jh.employee_id = e.employee_id
AND e.department_id = 10;Why This Is a Game-Changer
- No need for complex subqueries
- Easier to write and understand
- Better performance in many cases
Previously, you had to rely on correlated subqueries, which were harder to read and maintain.
6. RETURNING Clause Enhancements
Oracle 23ai improves the RETURNING clause for UPDATE and MERGE.
Example
UPDATE employees
SET salary = salary * 1.1
RETURNING employee_id, salary INTO :emp_id, :new_salary;Benefits
- Fetch updated values immediately
- Reduce extra SELECT queries
- Improve application performance
7. IF EXISTS and IF NOT EXISTS Support
Conditional object handling is now much simpler.
Examples
DROP TABLE employees IF EXISTS;
CREATE TABLE employees (
id NUMBER
) IF NOT EXISTS;Why This Helps
- Avoid errors during deployment
- Simplify automation scripts
- Improve DevOps workflows
Real-World Impact for Developers
These features aren’t just syntactic sugar—they solve real problems.
Developer Productivity
- Less code to write
- Easier debugging
- More readable SQL
Performance Improvements
- Fewer round trips (multi-insert, returning clause)
- Optimized execution paths
Better Compatibility
Oracle is now closer to:
- PostgreSQL
- MySQL
- SQL Server
This makes it easier for developers switching between databases.
Practical Example Combining Features
Here’s a modern Oracle 23ai query using multiple new features:
ALTER SESSION SET group_by_position_enabled = TRUE;
INSERT INTO departments (id, name)
VALUES (10, 'Engineering'), (20, 'Marketing');
SELECT department_id, AVG(salary)
FROM employees
GROUP BY 1;
UPDATE employees e
SET salary = j.max_salary
FROM jobs j
WHERE e.job_id = j.job_id;Clean, readable, and efficient.
Final Thoughts
Oracle Database 23ai is a significant upgrade that focuses heavily on developer experience and SQL simplicity. Features like:
- SELECT without FROM
- GROUP BY alias/position
- Multi-row inserts
- Direct joins in UPDATE/DELETE
…all contribute to making Oracle more modern and easier to work with.
If you’re a DBA or developer, adopting these features can:
- Reduce development time
- Improve code quality
- Make your SQL more maintainable
Recommendation
While this article covered key highlights, Oracle 23ai includes hundreds of additional features. It’s worth exploring the official documentation to fully understand its capabilities.
Start small—try replacing DUAL, use multi-row inserts, and experiment with direct joins. You’ll quickly notice how much smoother your SQL development becomes.
