DUAL is a special one-row, one-column table in Oracle data dictionary.
Of what use is DUAL table in Oracle?
DUAL comes in handy when you want to select just one row through a query. Oracle SQL structure requires you to have a FROM <table> clause, but some queries don’t need a table — if you want to know the current system date, for example, or the answer for (3+1)*5. DUAL is useful for queries you’d write for such cases:
select user from dual;
select (3+1)*5 from dual;
But why DUAL? Won’t any table with a rownum < 2 filter work equally well?
Yes, why not simply:
select user from scott.emp where rownum < 2;
This query gives you the same result but DUAL has an edge over any other table in the query — the Oracle optimizer recognizes DUAL as a special table and prepares the best execution plan for it.
What will happen if I insert more rows or delete from DUAL?
The Oracle optimizer trusts DUAL to have exactly one row — no more, no less. If you mess up its data you can lead to unpredictable behavior in the database. Don’t try it!
If DUAL is supposed to have only one row, why is it called DUAL? Why not SINGLE?!
According to this old article on the history of Oracle’s DUAL table, DUAL was originally not meant to be seen itself but instead used inside a view that was expected to be queried. The idea was that you could do a join to the DUAL table and create two rows in the result for every one row in your table. In that context, the name DUAL seemed fine.
