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 users.

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

Oracle creates DUAL when it installs the data dictionary. ($ORACLE_HOME/rdbms/admin/dcore.bsq)

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.