Dual Table in Oracle

Posted by AbundantCode

The Oracle DUAL table, which is a particular table used for evaluating expressions or invoking functions, will be covered in this tutorial.

A FROM clause is required in Oracle SELECT statements. Some queries, however, do not necessitate the use of a table, such as:

SELECT
  UPPER('This is a string')
FROM
  what_table;

You might consider building a table and utilising it in the FROM clause instead of using the UPPER() function in this situation.

Fortunately, Oracle supplies you with the DUAL table, which is a special table that is part of the SYS user’s schema but available to all users.

The DUAL table includes one row with the value X and one column named DUMMY with the data type VARCHAR2().

SELECT * FROM dual;

You can use the DUAL table to run queries that contain functions that don’t need any tables, such as the UPPER() function, as shown below:

SELECT
  UPPER('This is a string')
FROM
  dual;

You can utilise expressions in the SELECT clause of a query that accesses the DUAL table in addition to utilising built-in functions:

SELECT
  (10 + 5)/2
FROM
  dual;

Because it was created for quick access, the DUAL table is the most basic one.

Oracle handles the use of DUAL as if it were a function that merely evaluates the expression in the choose list in Oracle 10g release 1 and later. This optimization outperforms simply accessing the physical DUAL table in terms of performance.

Tags: