The DUAL table is automatically created by Oracle and contains one column (DUMMY) and one row (x value).

This table is often used by SQL developer in PL/SQL code (Package, Functions, Trigger) to initialize variables storing technical information such as for example SYSDATE, USER or HOSTNAME.

Querying DUAL table is generally faster  as we can see below:

SQL> select sysdate from dual;

SYSDATE
---------
05-OCT-21

Elapsed: 00:00:00.01

Execution Plan
----------------------------------------------------------
Plan hash value: 1388734953

-----------------------------------------------------------------
| Id  | Operation        | Name | Rows  | Cost (%CPU)| Time     |
-----------------------------------------------------------------
|   0 | SELECT STATEMENT |      |     1 |     2   (0)| 00:00:01 |
|   1 |  FAST DUAL       |      |     1 |     2   (0)| 00:00:01 |
-----------------------------------------------------------------


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
          0  consistent gets
          0  physical reads
          0  redo size
        554  bytes sent via SQL*Net to client
        386  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

 

But what happens when this “SELECT FROM DUAL” is executed several times into the application :

Let’s execute it for 100, 1000, 10000, 100000 and 1000000 executions

SQL> declare
        v_date date;
begin
 for rec in 1..100 loop
        select sysdate into v_date from dual;
  end loop;
end;  2    3    4    5    6    7
  8  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.01
SQL> declare
        v_date date;
begin
 for rec in 1..1000 loop
        select sysdate into v_date from dual;
  end loop;
end;  2    3    4    5    6    7
  8  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.01
SQL> declare
        v_date date;
begin
 for rec in 1..10000 loop
        select sysdate into v_date from dual;
  end loop;
end;
  2    3    4    5    6    7    8
  9  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.08
SQL> declare
        v_date date;
begin
 for rec in 1..100000 loop
        select sysdate into v_date from dual;
  end loop;
end;  2    3    4    5    6    7
  8  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.85
SQL> declare
        v_date date;
begin
 for rec in 1..1000000 loop
        select sysdate into v_date from dual;
  end loop;
end;
  2    3    4    5    6    7    8
  9  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:08.34
SQL>

 

Let’s execute now a PL/SQL block to assign directly the variable v_date with SYSDATE instead of using “SELECT FROM DUAL”:

SQL> declare
        v_date date;
begin
 for rec in 1..100 loop
        v_date := sysdate;
  end loop;
end;
  2    3    4    5    6    7    8
  9  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.00
SQL> declare
        v_date date;
begin
 for rec in 1..1000 loop
        v_date := sysdate;
  end loop;
end;
  2    3    4    5    6    7    8
  9  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.00
SQL> declare
        v_date date;
begin
 for rec in 1..10000 loop
        v_date := sysdate;
  end loop;
end;
  2    3    4    5    6    7    8
  9  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.01
SQL> declare
        v_date date;
begin
 for rec in 1..100000 loop
        v_date := sysdate;
  end loop;
end;
  2    3    4    5    6    7    8
  9  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.14
SQL> declare
        v_date date;
begin
 for rec in 1..1000000 loop
        v_date := sysdate;
  end loop;
end;
  2    3    4    5    6    7    8
  9  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:01.28
SQL>

 

Conclusion:

Nb of executions SELECT FROM DUAL Assigning variable
100
1 ms
0 ms
1000 1 ms
0 ms
10000 8 ms
1 ms
100000 85 ms
14 ms
1000000 8.34 sec
1.28 sec

 

“SELECT FROM DUAL” is always slower than “Assigning variable”:

  • 8 times more slower for 10000 executions
  • 6 times more slower for 100000 executions
  • More than 8 times more slower for 1000000 executions

From a performance point of vew, “SELECT FROM DUAL” must be avoided to initialize variable because when you query the DUAL table in a PL/SQL block, oracle optimizer does a roundtrip between the PL/SQL engine and the SQL engine. For few rows, it’s fast, but for several rows (Ex.: 1000000) the “SELECT FROM DUAL” is inefficient because oracle will do 1000000 roundtrips between the PL/SQL engine and the SQL engine.

I have seen plenty of applications where the “SELECT FROM DUAL” is used everywhere (Ex. : “SELECT FROM DUAL” in a Logon trigger !!!) while we can use a simple “Assigning variable” and changing the code has increased the performance of the application significantly.

 


Thumbnail [60x60]
by
Lazhar Felahi