Quick Search:
CODE
Oracle PL/SQL Code Library
JOBS
Find Or Post Oracle Jobs
FORUM
Oracle Discussion & Chat
 The Oracle DUAL Table      [Return To Index] Jump to:  
  Looking for the original pages? (formerly called "Morgan's Library") You can find them here.

Term: DUAL

Definition:
In Oracle, DUAL is a table which is created with every installation of Oracle along with the Data Dictionary (in fact, DUAL is actually a part of the Data Dictionary). It consists of exactly one column named "dummy" and a single row. The value of that row is "X". DUAL is owned by SYS but DUAL can be accessed and used by every user regardless of their role or assigned permissions.

Because DUAL contains just one row, it is guaranteed to return one and only one row in SELECT statements. DUAL is used for all sorts of things, including date arithmetic. You can think of DUAL as a convenience table. One nice thing about DUAL is that the optimizer understands DUAL is a special one row, one column table, so when you use it in queries, it uses this knowledge when developing the plan.

The DUAL table's column, the column name, its datatype and even its value are not important. Technically it could be any datatype. DUAL exists just so we have a 1 row table we can reliably select from.

The DUAL table was created by Chuck Weiss of Oracle corporation to provide a table for joining in internal views. According to Chuck, the original DUAL table had two rows in it (hence its name), but subsequently it only had one row. Versions of the Oracle database starting with 10g have been optimized so that the database no longer performs physical or logical IO on the DUAL table even though the DUAL table still actually exists.

Don't EVER add rows to DUAL, fiddle with its column, or alter it in any way. If you do, you can expect some very strange and potentially destructive things to happen in your database, assuming the database doesn't just crash and burn completely.


Related Links:

Related Code Snippets:
 
Home      :      Code Library      :      Sponsors      :      Privacy      :      Terms of Use      :      Contact Us 58 users online    © 2009 psoug.org
PSOUG LOGIN
Username: 
Password: 
Forgot your password?