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.
Not every idea maps cleanly to a shipped feature. This one is more speculative — and I want to be honest about that.
In 2016, I also posted an idea I called “CTAS with built-in Autodrop”. The concept was a CREATE TABLE AS SELECT that included a lifecycle clause — a way to define, at creation time, that the table should be automatically dropped (or archived) after a certain time or condition.
CREATE TABLE temp_report AS
SELECT * FROM orders WHERE order_date < SYSDATE - 30
AUTODROP AFTER 7 DAYS;The motivation: temporary working tables created for reports, ETL staging, or analysis are often forgotten. They accumulate. Giving the database a built-in way to manage their lifecycle would reduce clutter and DBA overhead.
In Oracle 12c, Oracle shipped Automatic Data Optimization (ADO) — a feature under the Information Lifecycle Management umbrella that automatically manages data based on policies (heat maps, tiering, compression, archiving). My CTAS Autodrop idea predated ADO’s expanded feature set, though some of ADO’s core concepts were already in the works.
Did my idea seed any part of ADO? Honestly, I don’t know. I said as much in my email to Gerald Venzl:
“It was suggested before the Automatic Data Optimization (ADO) was released, so it could have been something like a seed to all of it. But then I may not be able to claim, exactly.”
That’s fair. ADO is far broader than my suggestion. The connection is more conceptual than syntactic.
What I will say: the Oracle Ideas forum in 2016 was read by product managers. Ideas were evaluated. And this one described a gap that Oracle recognized and eventually addressed — in their own way, on their own timeline.
Link to my original 2016 post: https://forums.oracle.com/ords/apexds/post/ctas-with-built-in-autodrop-5261
