Oracle Dual Table

Summary: in this tutorial, you will learn about the Oracle DUAL table which is a special table used for evaluating expressions or calling functions.

In Oracle, the SELECT statement must have a FROM clause. However, some queries don’t require any table for example:

SELECT
  UPPER('This is a string')
FROM
  what_table;Code language: SQL (Structured Query Language) (sql)

In this case, you might think about creating a table and use it in the FROM clause for just using the UPPER() function.

Fortunately, Oracle provides you with the DUAL table which is a special table that belongs to the schema of the user SYS but it is accessible to all users.

The DUAL table has one column named DUMMY whose data type is VARCHAR2() and contains one row with a value X.

SELECT * FROM dual;Code language: SQL (Structured Query Language) (sql)

By using the DUAL table, you can execute queries that contain functions that do not involve any table like the UPPER() function as follows:

SELECT
  UPPER('This is a string')
FROM
  dual;Code language: SQL (Structured Query Language) (sql)

Besides calling built-in functions, you can use expressions in the SELECT clause of a query that accesses the DUAL table:

SELECT
  (10 + 5)/2
FROM
  dual;Code language: SQL (Structured Query Language) (sql)

The DUAL table is the most simple one because it was designed for fast access.

In Oracle 10g release 1 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.