Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
SQL> desc dual
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 DUMMY                                              VARCHAR2(1)

SQL> select 4*5 from dual;

       4*5
----------
        20

SQL>

I find it really strange. If there is no column named 4*5 in dual, how does the select statement work?

Also, why don't I see the same behaviour when I create my own dual table?

SQL> create table dual2(dummy varchar2(1)); 

Table created.

SQL> desc dual2
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 DUMMY                                              VARCHAR2(1)

SQL> select 4*5 from dual2;

no rows selected

SQL> 
share|improve this question
8  
As a note, mate, these questions can be trivially discovered by looking at the documentation. –  Brian Ballsun-Stanton Jan 9 '11 at 10:03
1  
We once even succeeded in creating our own DUAL table shading the oracles DUAL table and were able to insert and fetch more than one row from it. –  bernd_k Jan 9 '11 at 10:06
1  
Interview Question .....good note for students –  vettipayyan Jan 9 '11 at 16:28
4  
Your dual2 example would work if you put a single row in that table. Expressions made up of constants evaluate to their result regardless of table contents, but they need a row or rows to attach to in order to be returned. –  Noah Yetter Sep 15 '11 at 19:19

9 Answers 9

up vote 21 down vote accepted

From Wikipedia:

The DUAL table is a special one-row table present by default in all Oracle database installations. It is suitable for use in selecting a pseudocolumn such as SYSDATE or USER. The table has a single VARCHAR2(1) column called DUMMY that has a value of 'X'.

Thus, the dual table is a way to perform operations against what amounts to be an empty but not null table. This is useful when one doesn't care about the table, but needs to perform operations through a select statement. If the table had more than one row or column, multiple results would be returned (due to operating over the entire set of tuples when performing the operation.)

It shouldn't be used in production, unless you specifically need to invoke certain procedures through SQL.

4*5 is a mathematical operation, just as 'Foo' is a string. Thus, just as one can select 4*5 from any table, just as one can select 'Foo' from any table, DUAL is a way of selecting it from a known-good table that will never have multiple results.

From the documentation (CONCEPTS):

DUAL is a small table in the data dictionary that Oracle Database and user-written programs can reference to guarantee a known result. The dual table is useful when a value must be returned only once, for example, the current date and time. All database users have access to DUAL.

The DUAL table has one column called DUMMY and one row containing the value X.

And the SQL Reference:

DUAL is a table automatically created by Oracle Database along with the data dictionary. DUAL is in the schema of the user SYS but is accessible by the name DUAL to all users. It has one column, DUMMY, defined to be VARCHAR2(1), and contains one row with a value X. Selecting from the DUAL table is useful for computing a constant expression with the SELECT statement. Because DUAL has only one row, the constant is returned only once. Alternatively, you can select a constant, pseudocolumn, or expression from any table, but the value will be returned as many times as there are rows in the table. Refer to "About SQL Functions" for many examples of selecting a constant value from DUAL.

Beginning with Oracle Database 10g Release 1, logical I/O is not performed on the DUAL table when computing an expression that does not include the DUMMY column. This optimization is listed as FAST DUAL in the execution plan. If you SELECT the DUMMY column from DUAL, then this optimization does not take place and logical I/O occurs.

share|improve this answer
    
Why am I not able to select 4*5 from my dual2 table? –  Lazer Jan 9 '11 at 9:56
1  
Dual2 has no rows in the table and should only be used when one doesn't want results back. –  Brian Ballsun-Stanton Jan 9 '11 at 9:59
2  
@Lazer : when asking for nothing by constants or non-table variables, you'll get back a number of rows equal to the number of rows in the table (1 for DUAL, 0 for DUAL2), so if you do your match against a 4000 row table, you'll get back the answer 4000 times. –  Joe Jan 9 '11 at 13:28
1  
"It shouldn't be used in production, unless you specifically need to invoke certain procedures through SQL" Why not? –  Nick Pierpoint Aug 22 '11 at 16:20

DUAL is a table with has exactly one row as the following SQL statement will show:

SELECT * FROM dual;

Your dual2 table has no rows. If you insert one, you will see the same behavior.

4*5 is an expression that Oracle can evaluate without actually using data from the table. It will evaluate it once for every row, just like it would do with a normal column expression. So if there is no row, no result is returned, if there are two rows, you will get the 20 twice.

share|improve this answer

The dual table "works" almost just the way any other table works: it is a table from which you can select records.

This means, for example, you can describe the table. Here, in SQL*Plus:

SQL> set lines 50
SQL> desc dual
Name                    Null?    Typ
----------------------- -------- ----------------
DUMMY                            VARCHAR2(1)

So, the table has one column, named dummy which is a varchar2(1).

The table has, by design, one record (at least if nobody fiddled with it):

SQL> select count(*) from dual;

COUNT(*)
----------
         1

So, in order to get the same behaviour with dual2 as you have with dual, you have to insert one record into dual. Better yet, create it with a create table as select (ctas):

SQL> create table dual2 as select * from dual;

Now, your query works:

SQL> select 4*5 from dual2;
       4*5
----------
        20

Earlier, I said that dual almost works like any other table. So, when does it not work like any other table?

It behaves differently, if no value from the table itself is selected. Again, with your queries, I let Oracle explain them ...

SQL> set lines 150
SQL> explain plan for select 4*5 from dual2;

EXPLAIN PLAN ausgef³hrt.

... in order to see how the table is accessed:

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------
Plan hash value: 3445655939

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

It can be seen that the statement does a full table access on dual2.

Now, same thing with dual:

SQL> explain plan for select 4*5 from dual;

EXPLAIN PLAN ausgef³hrt.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------
Plan hash value: 1388734953

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

This is where the dual table behaves differently: the value of dummy is not needed, so a fast dual operation is executed, in order for the instance not to read the actual value on the disk.

share|improve this answer

In addition to other answers, Oracle isn't so picky about the spaces SQL text (in some places at least). The SQL parser also tokenizes by character class differences in some cases, not just by whitespace.

For example, you can run such statements:

SQL> select*from dual;

D
-
X


SQL> select(1)from dual;

       (1)
----------
         1

SQL> select-null from dual;

     -NULL
----------


SQL> select-1 from dual;

        -1
----------
        -1

SQL> 

It's also possible to run SQL without any whitespace in it:

SQL> select*from/**/dual;

D
-
X

I have some more examples here:

http://blog.tanelpoder.com/2008/01/14/can-you-write-a-working-sql-statement-without-using-any-whitespace/

Tanel Poder

share|improve this answer

Incidentally, DUAL is one of the few 'tables' that works when the instance has been started but the database hasn't been opened.

You get something like

ADDR     INDX   INST_ID D
-------- ------ ------- -
0C0362D4      0       1 X
share|improve this answer

A fast dual operation re-writes your code to query x$dual. Because this "table" is a C data structure in the SGA, you can query it in nomount mode.

share|improve this answer

For what it's worth, it works in exactly the same way in MySQL.

mysql> use test;
Database changed

mysql> create table fred(billy int);
Query OK, 0 rows affected (0.79 sec)

mysql> select 4 + 5 from fred;
Empty set (0.00 sec)

mysql> select 4 + 5 as mary from fred;
Empty set (0.00 sec)

mysql> insert into fred values(1);
Query OK, 1 row affected (0.13 sec)

mysql> select 4 + 5 from fred;
+-------+
| 4 + 5 |
+-------+
|     9 |
+-------+
1 row in set (0.00 sec)

mysql> select 4 + 5 as mary from fred;
+------+
| mary |
+------+
|    9 |
+------+
1 row in set (0.00 sec)

mysql> insert into fred values(2);
Query OK, 1 row affected (0.08 sec)

mysql> select 4 + 5 from fred;
+-------+
| 4 + 5 |
+-------+
|     9 |
|     9 |
+-------+
2 rows in set (0.00 sec)

mysql> select 4 + 5 as mary from fred;
+------+
| mary |
+------+
|    9 |
|    9 |
+------+
2 rows in set (0.00 sec)

mysql> explain select 4 + 5 as mary from fred;
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
|  1 | SIMPLE      | fred  | ALL  | NULL          | NULL | NULL    | NULL |    2 | NULL  |
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
1 row in set (0.00 sec)

mysql> 

And it also appears that DUAL is some sort of in memory structure in MySQL also. Note the difference in the two explain plans - "no tables used" for DUAL in MySQL.

Interestingly however, I cannot do a DESC on MySQL's dual, which is different to Oracle - but it was introduced specifically AIUI to allow Oracle syntax to work on MySQL.

mysql> select 4 + 5 from dual;
+-------+
| 4 + 5 |
+-------+
|     9 |
+-------+
1 row in set (0.00 sec)

mysql> explain select 4 + 5 from dual;
+----+-------------+-------+------+---------------+------+---------+------+------+----------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra          |
+----+-------------+-------+------+---------------+------+---------+------+------+----------------+
|  1 | SIMPLE      | NULL  | NULL | NULL          | NULL | NULL    | NULL | NULL | No tables used |
+----+-------------+-------+------+---------------+------+---------+------+------+----------------+
1 row in set (0.00 sec)

mysql> desc dual;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'dual' at line 1
mysql> 
share|improve this answer

In the oracle database, Dual table is basically used to get value of pseudo-columns. It contains following properties:

  1. It is owned by the sys user
  2. It is available for all the users
  3. It contains only one column which name is dummy with datatype Varchar2(1) , This column can have maximum width of one character.

If you want to get more detail, check here

share|improve this answer

The question is already answered. These are some notes to the purpose of the dual table. Dual can be used to evaluate expressions in a select clause. Many other database system don't need such a table for this purpose. MS Sql, MySql, Posgress can evaluate the following statement

select 3+5 

Oracle can't. An Oracle select statement always needs a "from"-clause.

Some funtions can't be used in pl/sql expression like DUMP.

So

declare
str varchar2(100);
begin
str:=dump('Hallo');
end;
/

will raise an exception but

declare
str varchar2(100);
begin
select dump('Hallo') into str from dual;
end;
/

will work.

It can be used to extend the result set of a query

select user_id,username from user_users
union all
select -1,'NO USER'
from dual
/

which gave

| USER_ID |     USERNAME |
|---------|--------------|
|  476267 | USER_4_E8C50 |
|      -1 |      NO USER |

or generate data with select queries like

with nlist(n) as (
select 1 from dual
union all
select n+1
from nlist 
where n<5    )
select n
from nlist
)

which returns

| N |
|---|
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |

in sqlfiddle

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.