Friday, March 4, 2011

What is Dual Table ?

Dual is a table which is created by oracle along with the data dictionary. It consists of exactly one column whose name is dummy and one record. The value of that record is X.  The reason to use the DUAL table isn't just portability, but optimization.  The owner of dual is SYS but dual can be accessed by every user.Dual is useful because it always exists, and has a single row, which is handy for select statements with constant expressions.

SQL> select 1+1  from dual ;
SQL> select sysdate from dual;


DUAL was originally a table and the database engine would perform disk IO on the table when selecting from DUAL. This disk IO was usually logical IO (not involving physical disk access) as the disk blocks were usually already cached in memory. This resulted in a large amount of logical IO against the DUAL table.Later versions of the Oracle database have been optimized and the database no longer performs physical or logical IO on the DUAL table even though the DUAL table still actually exists.

If we drop a dual table then it will have serious impact on the database functionality. so we should never drop dual table. TOM KYTE explained about this in more details .Click the below link 

Also Check this link for more about dual table :

Enjoy          J J J

No comments: