Search notes:
Oracle: The DUAL table
DUAL is the name of a
table with exactly
one record and
one column . The name of the column is
DUMMY and its value is
X.
Although it is possible to delete or update the record, or insert new records, one really should not do that .
The owner of
DUAL is
SYS, but it is accessibly by every
user .
Purpose of the DUAL table
The purpose of the DUAL table is to let a user evaluate an SQL-expression in an SQL statement.
Oracle 23c
Up to
21c , the value of an SQL-expression could only be calculated in an SQL statement that selected from
dual or in a
PL/SQL expression.
Starting with
23c , it's possible to select an expression simply like so:
select
sysdate as this_moment,
7*6 as result
;
Optimizer
The
optimizer «knows» that
dual has one record only. In fact, when querying an SQL expression from
dual, Oracle does not physically select the record (see the plan operation
FAST DUAL ):
The following statement is executed completely in memory:
select sysdate from dual;
Creation of DUAL
create table dual /* pl/sql's standard pckg requires dual. */
(dummy varchar2(1)) /* note, the optimizer knows sys.dual is single row */
storage (initial 1)
/
insert into dual values('X')
/
create public synonym dual for dual
/
Name
The name of this table is DUAL because it originally contained two rows.
The table was created by
Charles "Chuck" Weiss (the 26th Oracle employee?) and was meant to create reports with summaries of DATA and INDEX extents.
At one point in time, the second (or first) row was apparently removed from this table.
See also
The av_dual table.