Dual

DUAL dummy X

DUAL is a table owned by the SYS user that contains a single VARCHAR2 column called DUMMY and a single row with the value 'X' in it. This table is handy when you want to select a pseudocolumn such as SYSDATE or simply select an expression and only want to get a single row back.

History

The DUAL table was created by Chuck Weiss, an employee of Oracle Corporation. As it originally contained two rows, this table was named DUAL. He explained the purpose of DUAL in the January/February 2004 issue of Oracle Magazine as:

I created the DUAL table as an underlying object in the Oracle Data Dictionary. It was never 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. Then, by using GROUP BY, the resulting join could be summarized to show the amount of storage for the DATA extent and for the INDEX extent(s). The name, DUAL, seemed apt for the process of creating a pair of rows from just one.

Structure and content

Describe the table:

SQL> DESC sys.dual
Name                            Null?    Type
------------------------------- -------- -----------------------
DUMMY                                    VARCHAR2(1)

Look at the data in the table:

SELECT * FROM sys.dual;

Performance

Many systems depend heavily on the DUAL table and run repetitive queries against it. A lot of combined I/O are thus unnecessarily performed to read this table (physical and logical I/O). However, starting with Oracle 10g the database was optimised and no longer performs any I/O against DUAL unless the table's data should be displayed. The function provided by DUAL was somehow internalized and the query optimizer will intelligently know what to do when this table is encountered in a query.

Explain plan without optimization (for example: select * from dual):

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |     2 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| DUAL |     1 |     2 |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------

Explain plan with optimization (for example: select sysdate from dual):

-----------------------------------------------------------------
| Id  | Operation        | Name | Rows  | Cost (%CPU)| Time     |
-----------------------------------------------------------------
|   0 | SELECT STATEMENT |      |     1 |     2   (0)| 00:00:01 |
|   1 |  FAST DUAL       |      |     1 |     2   (0)| 00:00:01 |
-----------------------------------------------------------------

Sample usage

Get the current date:

SELECT sysdate FROM dual;

Select the USER pseudocolumn:

SELECT USER from dual;

Do some maths:

SELECT 7*8/9 FROM dual;

Extract the next value from a sequence:

SELECT myseq.NEXTVAL FROM dual;

Glossary of Terms
A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	#