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.
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.
With
Oracle Database 23ai, the humble INSERT statement has received some genuinely
useful upgrades. What was once a straightforward data-loading operation now
supports more advanced practical use cases, especially for modern
applications. Whether you’re working with AI-related data, handling high-volume
inserts, or trying to simplify everyday SQL logic, these new features are worth
paying attention to. Oracle has clearly focused on reducing complexity while
improving performance and flexibility. In this blog, I’ll walk through four
notable INSERT enhancements in Oracle 23ai. Each one addresses a real-world
problem that developers and DBAs commonly face.
Oracle
Database 23ai introduces a major evolution of the INSERT statement, modernizing
one of the most frequently used DML operations in Oracle SQL. These
enhancements are designed to make data insertion clearer, safer, and more
flexible, especially in environments with wide tables, evolving schemas, and
bulk data loads. New capabilities such as INSERT … SET, INSERT … BY NAME,
multi-row inserts using the VALUES clause, and support for non-numeric column
assignments in SET-based inserts reduce reliance on column order, simplify SQL
syntax, and help prevent common data-mapping errors. Together, these features
significantly improve code readability and developer productivity while
aligning Oracle SQL more closely with modern database standards.
For many years, writing SQL meant dealing with rules that often
felt unnecessary. Developers followed them because that was the only way things
worked, even when it made queries longer or harder to read. With Oracle
Database 23ai, things are starting to improve. Oracle has added new features
that make SQL easier to write and easier to understand. In this blog, I’ll
cover three of those improvements: aggregating interval data types, using
column aliases in GROUP BY, and grouping by column position. These changes may
seem small, but they can make a big difference in everyday SQL work.
Interval Data Type Aggregation
Working with time durations in SQL has always been awkward.
Storing an interval was never the problem; the trouble started when you wanted
to do something useful with it. Simple questions like “What’s the total
time spent?” or “What’s the average duration?” often meant converting intervals
into seconds or minutes, running the math, and then converting everything back
again. It worked, but it wasn’t pretty, and it definitely wasn’t intuitive.
Oracle Database 23ai finally fixes this. Intervals can now be used
directly with aggregate functions like SUM and AVG. This means you can work
with durations as durations, without going through manual conversions. The
database handles the math for you and returns a proper interval result.
Example:
In this example, we’ll look at a support system that tracks how
long each ticket takes to resolve. The resolution time is stored using the INTERVAL
DAY TO SECOND data type, which is a very natural way to represent durations.
First, we create a table and insert a few sample tickets with
different resolution times:
-- Setup: Create a table with interval data
CREATE TABLE support_tickets (
ticket_id NUMBER,
resolution_time INTERVAL DAY TO SECOND
);
INSERT INTO support_tickets VALUES (1, INTERVAL '0 02:30:00' DAY TO SECOND);
INSERT INTO support_tickets VALUES (2, INTERVAL '0 01:15:00' DAY TO SECOND);
INSERT INTO support_tickets VALUES (3, INTERVAL '1 04:45:00' DAY TO SECOND);
Each row represents how long a ticket took to close, ranging from
just over an hour to more than a full day.
Now, using Oracle 23ai, we can calculate the total and average
resolution time directly:
-- Oracle 23ai: Direct aggregation
SELECT
SUM(resolution_time) AS total_time,
AVG(resolution_time) AS avg_time
FROM support_tickets;
Before Oracle 23ai, this query would fail because SUM and AVG did
not support interval data types. Developers had to convert intervals into
numbers, perform the calculation, and then manually format the result back into
a readable duration. In 23ai, Oracle handles this natively. The database understands how
to add and average intervals and returns the result as a properly formatted INTERVAL
DAY TO SECOND.
GROUP BY Alias
Repeating long expressions in the GROUP BY clause has always been one of Oracle SQL’s biggest annoyances. If you used a CASE expression or formatted value in the SELECT, you had to copy it exactly into the GROUP BY.
Oracle 23ai finally fixes this by allowing you to reference column aliases directly. This makes queries shorter, clearer, and much easier to maintain.
Example:
In this example, employees are grouped into salary bands using a CASE expression. Instead of repeating the entire expression in GROUP BY, I simply reuse the alias salary_level.
SELECT
CASE WHEN salary > 10000 THEN 'High' ELSE 'Standard' END AS salary_level,
COUNT(*) AS total_staff
FROM employees
GROUP BY salary_level;
Group by Position
When you write GROUP BY queries, you often end up repeating the
same columns or expressions that already appear in the SELECT list. This can
get annoying, especially when those expressions are long or hard to read.
Oracle Database 23ai makes this easier by allowing you to group by the position
of columns instead of typing them again.
This works similarly to ORDER BY 1, 2. Instead of repeating
column names or functions, you can simply refer to where they appear in the SELECT
list. While it’s still a good idea to use column names in long-term or
production code.
Example:
Since this is a new feature, it needs to be turned on for your session. If you don’t enable it, Oracle will treat the numbers as literal values and raise an error.
-- Step 1: Enable the feature for your current session
ALTER SESSION SET group_by_position_enabled = TRUE;
NOTE: If you didn’t enable it, you will encounter the following
error
Now let’s look at a simple sales example. We want to group sales
data by region and by year, and then calculate the total sales amount.
-- Step 2: Group sales data using column positions
SELECT
region_name,
EXTRACT(YEAR FROM sale_date) AS sale_year,
SUM(amount) AS total_sales
FROM (
SELECT 'North' AS region_name, DATE '2023-01-01' AS sale_date, 100 AS amount FROM dual UNION ALL
SELECT 'South' AS region_name, DATE '2023-05-15' AS sale_date, 250 AS amount FROM dual
)
GROUP BY 1, 2;
In this query, GROUP BY 1, 2 tells Oracle to group the results
using the first and second columns from the SELECT list. The first
column is region_name, and the second column is the year extracted from sale_date.
Conclusion
Oracle 23ai’s SQL enhancements show a welcome shift toward
practicality and developer comfort. Being able to aggregate interval data
directly, group by aliases, and group by position removes a lot of unnecessary
repetition that used to clutter otherwise simple queries. The result isn’t just
shorter SQL—it’s SQL that’s easier to read, reason about, and maintain over
time. These features make refactoring older queries genuinely worthwhile, not
just cosmetic. If you’re working with Oracle 23ai, it’s well worth revisiting
existing SQL and taking advantage of these small but meaningful improvements.
The PL/SQL engine is a virtual
machine that resides in memory and processes the PL/SQL m-code instructions.
When the PL/SQL engine encounters an SQL statement, a context switch is made to
pass the SQL statement to the Oracle server processes. The PL/SQL engine waits
for the SQL statement to complete and for the results to be returned before it
continues to process subsequent statements in the PL/SQL block.
In other words, context switching in
Oracle databases refers to the overhead incurred when control shifts between
the SQL engine and the PL/SQL engine during the execution of mixed SQL and
PL/SQL code. The two engines operate in different runtime environments, so when
you run SQL statements within PL/SQL code, the database has to 'switch' between
these environments. This switching can cause performance degradation.
Automatic PL/SQL to SQL Transpiler
PL/SQL functions within SQL
statements are automatically converted (transpiled) into SQL expressions
whenever possible.
