Category: Database

THE SAGA: “The Features I Suggested, Oracle Implemented — A Personal History”

This is a five-part series about two Oracle SQL features introduced in Oracle Database 23ai that are strikingly similar — in concept, motivation, and syntax — to suggestions I posted in the Oracle Community forums in July 2016, nearly a decade before they were implemented.

On July 2, 2016, I posted an idea to the Oracle Community Database Ideas forum. The title was “SQL Syntax: GROUP BY OTHERS”.

Here’s the core of what I proposed:

“So, to make it simpler, it could exists a ‘GROUP BY OTHERS’ that group all selected columns that are not under aggregate functions.”

I demonstrated it with this example:

SELECT COUNT(*), SUM(salary), AVG(salary), AVG(age),
       company, state, country
FROM company_employees
GROUP BY OTHERS;

My point was clear: developers shouldn’t have to repeat every non-aggregated column in the GROUP BY clause. The database should be smart enough to infer it. I even acknowledged that the keyword didn’t have to be OTHERS — I mentioned GROUP BY all, GROUP BY everythingelse, among others. The idea was the important part.

In Oracle 23ai (released May 2024), the feature landed as GROUP BY ALL. The syntax:

SELECT COUNT(*), SUM(salary), AVG(salary), company, state, country
FROM company_employees
GROUP BY ALL;

The motivation is identical. The behavior is identical. The keyword ALL was one of the alternatives I explicitly mentioned in a comment on my own post.

I’m not saying this proves Oracle implemented my idea. What I’m saying is that the timing of my suggestion — 2016 — and the implementation — 2024 — represents roughly an eight-year gap, during which the Oracle Ideas forum was maintained by Oracle’s product management team. These ideas were read. They were evaluated. And this particular one was implemented.

A credit in the documentation would have been appropriate. In my opinion, it still would be.

Link to my original 2016 post: https://forums.oracle.com/ords/apexds/post/sql-syntax-group-by-others-0435

THE SAGA: “The Features I Suggested, Oracle Implemented — A Personal History”

This is a five-part series about two Oracle SQL features introduced in Oracle Database 23ai that are strikingly similar — in concept, motivation, and syntax — to suggestions I posted in the Oracle Community forums in July 2016, nearly a decade before they were implemented.

Back in 2016, I was deep in the Oracle ACE program — eventually ACE Director for four years — and spending a lot of time in the Oracle Community forums. At the time, Oracle had a space called “Database Ideas” where community members could suggest improvements to the database.

I posted several ideas. Two of them, I believe, were implemented in Oracle Database 23ai, roughly eight years later. The features are GROUP BY ALL and SQL Annotations.

I want to be careful about how I frame this. I’m not claiming Oracle copied my ideas. Large engineering organizations like Oracle receive countless community suggestions, conduct internal research, and implement features based on their own roadmap priorities. It’s entirely possible — likely, even — that these features were conceived independently by Oracle’s SQL language team.

What I am saying is this: the similarities are striking. Not just in concept, but in the specific syntax and motivation I described. And I was never contacted, credited in the documentation, or mentioned in any release notes.

Over the next four weeks, I’ll walk through each feature:

• Sep 8: GROUP BY ALL — my 2016 suggestion vs. the 23ai implementation
• Sep 15: Annotations — my “Oracle Tagging for Objects” idea vs. the Annotations clause
• Sep 22: CTAS with Autodrop — a related idea that may have seeded ADO
• Sep 29: Reflections on being an uncredited contributor

I’m writing this series not to be bitter — I’m genuinely proud that ideas I had are now real Oracle features — but because I think it’s worth talking about the relationship between community feedback and product development. Transparency matters.

Oracle 23ai introduces Priority Transactions — a mechanism to assign priorities to DML transactions so that higher-priority work can preempt lower-priority work when lock contention occurs.

In a busy OLTP system, a long-running batch job holding a lock can block critical customer-facing transactions. Until now, the only options were to kill the batch session manually or accept the latency. Priority Transactions automate this.

Setting a transaction priority:

-- High priority (e.g., customer checkout)
EXEC DBMS_TRANSACTION.SET_PRIORITY('HIGH');

-- Low priority (e.g., background batch)
EXEC DBMS_TRANSACTION.SET_PRIORITY('LOW');

How preemption works:

When a HIGH priority transaction attempts to acquire a lock held by a LOW priority transaction, Oracle evaluates the conflict. If the high-priority transaction has waited longer than a configurable threshold, Oracle can:

1. Abort the low-priority transaction (rollback and release locks)
2. Notify the low-priority session so it can self-abort cleanly
3. Defer the decision based on age and priority differential

Configuration:

ALTER SYSTEM SET TRANSACTION_PRIORITY_POLICY = 'PREEMPTIVE';
ALTER SYSTEM SET PRIORITY_TRANSACTION_WAIT_TIMEOUT = 5; -- seconds

Monitoring:

SELECT sid, serial#, priority, wait_class, seconds_in_wait
FROM   v$session
WHERE  wait_class = 'Concurrency'
ORDER BY priority DESC, seconds_in_wait DESC;

Real-world impact: In e-commerce and financial systems where a payment transaction must complete quickly, Priority Transactions ensure that a poorly-timed batch report doesn’t cause a customer-facing timeout. This is the kind of feature that previously required complex application-layer retry logic.

One of Oracle 23ai’s most interesting concurrency features is Lock-Free Reservations. It solves a classic high-concurrency problem: multiple sessions competing to update the same row — like decrementing inventory counts in a flash sale.

The classic problem:

-- Session 1 and Session 2 both try to decrement inventory
UPDATE products SET stock_qty = stock_qty - 1 WHERE product_id = 42;

With thousands of concurrent orders, Sessions 2 through N all block waiting for Session 1’s lock. This creates a hot row contention bottleneck.

Lock-Free Reservation solution:

-- Define the column as a reservation target
ALTER TABLE products MODIFY (
    stock_qty NUMBER RESERVABLE CONSTRAINT stock_non_negative CHECK (stock_qty >= 0)
);

Once a column is declared RESERVABLE, Oracle handles concurrent updates to it using a reservation mechanism rather than row-level locking. Each session “reserves” a portion of the value without blocking others.

How it works under the hood:

Oracle maintains a pending reservation table alongside the main table. Each concurrent update is stored as a reservation. The actual column value is the base value plus the sum of all pending reservations. When a session commits, its reservation is rolled into the base value.

The net effect:

• Multiple sessions can concrement stock_qty simultaneously without blocking each other
• The CHECK constraint (stock_qty >= 0) is still enforced — the sum of all reservations cannot drive the value below zero
• ACID guarantees are maintained

Ideal use cases: Inventory management, ticket booking, seat reservations, resource quotas — any scenario where many sessions update the same numeric column concurrently.

This is a sophisticated concurrency improvement that eliminates a well-known Oracle scalability bottleneck without requiring application-level workarounds.