Introduction
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:
-- 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.
