DEV Community

Cover image for `SELECT * FROM DUAL` - The Lore of Oracle DB's Weirdest Table
ShikariSohan
ShikariSohan

Posted on AI-assisted

`SELECT * FROM DUAL` - The Lore of Oracle DB's Weirdest Table

#oracle #database #sql #history

If you've ever written Oracle SQL, you've probably typed this:

SELECT SYSDATE FROM DUAL;
Enter fullscreen mode Exit fullscreen mode

I use DUAL constantly. Billing queries, rate validations, migration scripts. Yet for months, I never stopped to ask: why does this table even exist?

Then one afternoon, I ran:

SELECT * FROM DUAL;
Enter fullscreen mode Exit fullscreen mode 
DUMMY
-----
X
Enter fullscreen mode Exit fullscreen mode

One column. One row. The letter X.

That's it. So I went down the rabbit hole.

The Man Behind DUAL

The story goes back to Oracle's early days and an engineer named Charles "Chuck" Weiss.

DUAL was originally created as an internal object in Oracle's data dictionary. Weiss explained its purpose in Oracle Magazine: a view needed to produce two rows for every source row so it could represent data-extent and index-extent statistics separately.

His solution? Join the source data to a table that had two rows.

One row became two. A little grouping did the rest.

The table was never intended to become a fixture of everyday SQL. Its name reflected that original dual-row purpose.

Eventually, one row disappeared. The name stayed.

(Historical account and Weiss's explanation)

The Accident That Made It Famous

In Oracle releases before Release 23, a SELECT statement required a FROM clause.

Want to do basic math?

SELECT 2 + 2;
-- ORA-00923 on older Oracle releases
Enter fullscreen mode Exit fullscreen mode

Want today's date?

SELECT SYSDATE;
-- Also requires FROM on older releases
Enter fullscreen mode Exit fullscreen mode

But Oracle had DUAL: a convenient, one-row source for evaluating expressions.

SELECT SYSDATE FROM DUAL;
SELECT 2 + 2 FROM DUAL;
SELECT USER FROM DUAL;
Enter fullscreen mode Exit fullscreen mode

And just like that, an internal implementation detail became everyday developer syntax.

PostgreSQL, SQL Server, and SQLite let you evaluate expressions without a dummy table. MySQL does too, although it also supports DUAL for compatibility.

Oracle developers kept typing those five characters.

The Performance Plot Twist

For years, queries against DUAL could involve logical I/O. Then came FAST DUAL, introduced in Oracle Database 10g Release 1.

When a query doesn't reference the DUMMY column, Oracle can skip logical I/O against the table and return the expression result through this optimization.

The table still exists. The common query simply doesn't need to read it.

Want to see the difference? Compare these:

SELECT * FROM DUAL;
SELECT SYSDATE FROM DUAL;
Enter fullscreen mode Exit fullscreen mode

The first reads the table's actual contents. The second can use FAST DUAL.

That distinction matters more than you might expect.

What happens if you break it?

With sufficient privileges, you can modify or even drop SYS.DUAL. But don't experiment on a real database.

Deleting its row affects queries that read the actual table. Expression-only queries may behave differently because FAST DUAL can bypass that access. Dropping the table is far more dangerous: a documented Oracle 11g experiment produced ORA-01775: looping chain of synonyms and prevented a normal startup.

That's not a fun production incident. That's a career side quest.

Documented recovery experiment

The Ending: Oracle 23ai

Oracle finally made the FROM clause optional for simple expression queries in Release 23, first through the 23c Developer Release and later in the generally available Oracle Database 23ai release.

Now you can write:

SELECT SYSDATE;
SELECT 2 + 2;
Enter fullscreen mode Exit fullscreen mode

No DUAL required.

The table remains available for backward compatibility, but new code no longer needs it for these simple queries.

After decades of typing FROM DUAL, the workaround finally became optional.

Oracle's official feature guide

What I Actually Think About This

I write queries against DUAL at work all the time. Billing checks, rate validations, date comparisons. It's muscle memory.

And there's something beautiful about a table created to solve a narrow internal problem becoming a familiar part of SQL for generations of developers.

Chuck Weiss solved a problem. He didn't set out to create a feature that developers would keep using for decades.

Sometimes, engineering's strangest legacies aren't the things we design to last. They're the workarounds we never quite stop needing.

Previously: How I Discovered Java's Most Famous Inside Joke

References

Tags: #oracle #database #sql #programming #history

Top comments (0)