The Oracle DUAL table

The Oracle 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’.



Name                        Null?                Type
DUMMY                                             VARCHAR2(1)

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.




Why is it called “DUAL”?

The DUAL table was created by Chuck Weiss of Oracle corporation to provide a table for joining in internal views:

“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.” Chuck Weiss, Oracle

The original DUAL table had two rows in it (hence its name), but subsequently it only had one row.

Note: Although it is possible to delete the one record, or insert additional records, one really should not do that!.

2 Responses to The Oracle DUAL table

  1. mbt shoes says:

    Nice Information.. Thx for sharing this information!

    • imdjkoch says:

      Thanks a lot!

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: