Question theoretical. I study Oracle and meet this design as Dual. I understand that this is some virtual table, but I would like to understand the meaning of this thing, why and when it is applied.

Answer 1, Authority 100%

In the syntactically correct query, there is a section from.

Sometimes request / subquering is required, performing the formation of literals (or performing constant calculations), i.e. Not based on any table. For example, CTE generating a set of numbers or a list of dates. However, the FROM section in the query must be required – it is in this case that the from dual is used. Those. And the syntax is observed, and it is clearly said that the tables are not needed to execute the query. See below.

For example, the query requires a set of numbers from 1 to 9. The task is solved by the simplest CTE.

with CTE AS (Select 1 Num From Dual
       Union All.
       SELECT NUM + 1 From CTE WHERE NUM & LT; nine )

The Basic Part of the Union request returns a literal – a unit from which the recursive part dances. The data source (table) in this part of the Union request is no no in the riser. However, FROM must be. And comes to the rescue from dual .

Similarly, if the query requires a list of custom literal values. For example

select ...
From ...
Join (Select 2000 "Year" from Dual Union All
    SELECT 2005 From Dual Union All
    SELECT 2008 from Dual Union All
    Select 2013 from dual union all
    SELECT 2014 From Dual) "Years"
...

or if you need to perform some computations with constants

select ...
From ...
Join (Select 1024 bytes_to_kbytes,
       1024 * 1024 bytes_to_mbytes
       1024 * 1024 * 1024 bytes_to_gbytes
    From dual) koefficients
...

or variable

select ...
From ...
JOIN (SYSDATE, - @ DELTA1- @ DELTA2) period_start,
       Add_Months (Sysdate, - @ Delta2) period_end
    From dual) periods
...

If you contact the documentation (selecting from the dual table ), then:

Dual 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. IT HAS One Column, Dummy, Defined to Be Varchar2 (1), and Contains One Row with A Value X. Selecting from the Dual Table IS Useful for Computing A Constant Expression with the Select Statement. BECAUSE DUAL HAS Only One Row, The Constant Is Returned Only Once. Alternatively, You can Select a Constant, Pseudocolumn, OR Expression from Any Table, But The Value Will Be Returned As Many Times As There Are Rows in the table. Refer to “About SQL Functions” for Many Examples of SELECTING A Constant Value from Dual.

If short, then Dual is a physically real table that is in the user diagram SYS , but is accessible to reading any user. It has a single field named Dummy type varchar2 (1) and the only entry with the value of 'X' in this field. What actually guarantees that when used as a data source, only one final entry will be obtained. If you do not add to the dummy field, then when using the from dual physical access to the table does not occur (Fast Dual , right for Oracle Database 10G Release 1 and older).