Thursday, November 10, 2005
DUAL Table
What is the DUAL table?
The DUAL Dummy table (as it is sometimes called) is an automatically-generated table assigned to SYS, but accessible to all users. It has a single column "DUMMY" of type VARCHAR2(1) which has a single row with a value of 'X'.
scott@Robert> SELECT * FROM DUAL;
D
-
X
scott@Robert> DESC DUAL;
Name Null?
Type
----------------------------------------------------------------------- --------
-------------------------------------------------
DUMMY
VARCHAR2(1)
Warning: Do not modify the DUAL table! Why would you want to anyway? It is part of the data dictionary, which you shouldn't modify (that's a topic for another day).
What is it used for?
It is useful because it always exists, and has a single row, which is handy for select statements with constant expressions. You could just as easily do this with any other table with a single row, but using DUAL makes it portable among all Oracle installations.
Example:
The other reason to use the DUAL table isn't just portability, but optimization. According to Tom Kyte, the Oracle Optimizer knows it is a special one row, one column table. Eddie Awad tested a related assertion recently:
http://awads.net/wp/2005/11/09/dual-behavior-change/
Why is it called "DUAL"?
It was named "DUAL" because the primary intention of this table was to allow users to create 2 rows for every row in a table by joining it to this system table. Thus "DUAL". Chuck explained it in the January/February 2002 issue of Oracle Magazine:
http://www.oracle.com/technology/oramag/oracle/02-jan/o12sendmail.html
Of course, you can quite easily use DUAL to create as many rows as you want, using LEVEL (or CUBE). Just Ask Tom how this is done:
http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:40476301944675#40756986348091
Why is it sometimes referred to as a "magic" table?
For the answer to this and any remaining questions about DUAL, just Ask Tom:
http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:1562813956388#14583959098556
Links
Earlier I made mention of the DUAL table, including an example:
http://thinkoracle.blogspot.com/2005/06/nulls-in-oracle.html
Eddie Awad recently tested Jared Still's assertion of an interesting feature in Oracle 10g. Even if DUAL has more than one row, apparently you can trust it to always return just a single row. Read more:
http://awads.net/wp/2005/11/08/insert-into-dual/
Selecting from the DUAL table is mentioned in Chapter 8 of the Oracle SQL Reference Guide:
http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96540.pdf
The DUAL Dummy table (as it is sometimes called) is an automatically-generated table assigned to SYS, but accessible to all users. It has a single column "DUMMY" of type VARCHAR2(1) which has a single row with a value of 'X'.
scott@Robert> SELECT * FROM DUAL;
D
-
X
scott@Robert> DESC DUAL;
Name Null?
Type
----------------------------------------------------------------------- --------
-------------------------------------------------
DUMMY
VARCHAR2(1)
Warning: Do not modify the DUAL table! Why would you want to anyway? It is part of the data dictionary, which you shouldn't modify (that's a topic for another day).
What is it used for?
It is useful because it always exists, and has a single row, which is handy for select statements with constant expressions. You could just as easily do this with any other table with a single row, but using DUAL makes it portable among all Oracle installations.
Example:
SELECT 1+1 FROM DUAL;
1+1
----------
2
The other reason to use the DUAL table isn't just portability, but optimization. According to Tom Kyte, the Oracle Optimizer knows it is a special one row, one column table. Eddie Awad tested a related assertion recently:
http://awads.net/wp/2005/11/09/dual-behavior-change/
Why is it called "DUAL"?
It was named "DUAL" because the primary intention of this table was to allow users to create 2 rows for every row in a table by joining it to this system table. Thus "DUAL". Chuck explained it in the January/February 2002 issue of Oracle Magazine:
http://www.oracle.com/technology/oramag/oracle/02-jan/o12sendmail.html
Of course, you can quite easily use DUAL to create as many rows as you want, using LEVEL (or CUBE). Just Ask Tom how this is done:
http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:40476301944675#40756986348091
Why is it sometimes referred to as a "magic" table?
For the answer to this and any remaining questions about DUAL, just Ask Tom:
http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:1562813956388#14583959098556
Links
Earlier I made mention of the DUAL table, including an example:
http://thinkoracle.blogspot.com/2005/06/nulls-in-oracle.html
Eddie Awad recently tested Jared Still's assertion of an interesting feature in Oracle 10g. Even if DUAL has more than one row, apparently you can trust it to always return just a single row. Read more:
http://awads.net/wp/2005/11/08/insert-into-dual/
Selecting from the DUAL table is mentioned in Chapter 8 of the Oracle SQL Reference Guide:
http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96540.pdf
