Oracle dual table: what is dual table in oracle

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.

oracle dual
dual oracle

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.

what is dual table in oracle
what is dual table in oracle

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.

To read how to install oracle developer click here and for how to install oracle database click here.

Leave a Comment

Scroll to Top