Contents,Table of Contents, Page Contents
Oracle dual table
Oracle dual table 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.
Why dual oracle table is required
In oracle, SELECT statement requires FROM clause, which fetches data from oracle table. But in some scenarios, we have constant values which is not available in any table and we need to refer to these values in our SQL queries as additional value. To use these we require SQL DUAL table.
select lower('I AM AN ANALYST') from dual;
In the above example, we wanted to use a constant value ‘I AM AN ANALYST’ in a small case that requires to use of oracle LOWER() function. As we don’t have oracle table to execute this oracle statement. so we would use oracle dual table. The output will be like below.
dual table structure
DUAL has one column, DUMMY and it is defined to be VARCHAR2(1) as its data type and contains one row with a value X. We can use oracle DESCRIBE function to get data type of DUMMY COLUMN. The DUAL table is the most simple one because it was designed for fast access.
In Oracle 10g release and above, Oracle treats the use of DUAL the same as calling a function which simply evaluates the expression used in the select list. This optimization provides even better performance than directly accessing the physical DUAL table.
You can read about this from this link also.