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;


scott@Robert> DESC DUAL;
Name Null?
----------------------------------------------------------------------- --------

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.



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:

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:

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:

Why is it sometimes referred to as a "magic" table?

For the answer to this and any remaining questions about DUAL, just Ask Tom:


Earlier I made mention of the DUAL table, including an example:

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:

Selecting from the DUAL table is mentioned in Chapter 8 of the Oracle SQL Reference Guide:

Can we truncate dual table?
Can we create a synonym for package,function and trigger?
Post a Comment

<< Home

This page is powered by Blogger. Isn't yours?