Needless selects from DUAL

Of all the things I have seen done wrong over the years, selecting functions needlessly from DUAL is probably the most frequent.

DUAL is a table that is guaranteed to be present on every Oracle database, and it has a single column called 'DUMMY' with one row containing the value 'X':

SQL> select * from dual;

D
-
X

The DUAL table has some legitimate uses, such as generating data, and testing if a database connection is still alive. However, when I see it in production code it is almost always used needlessly to select a function, such as sysdate into a variable:

declare
  v_date date;
begin
  select sysdate 
  into v_date
  from dual;
end;
/

You many say there isn't anything wrong with that code, and it certainly works, but it works just as well without DUAL:

declare
  v_date date;
begin
  v_date := sysdate;
end;
/

I have also seen substr, instr, lpad, rpad and various other PLSQL functions being needlessly 'selected from dual', when the code would be shorter and faster if DUAL was not involved at all.

To prove the difference in speed between the two approaches, I used runstats to perform a benchmark:

set serveroutput on;

exec runstats_pkg.rs_start;

begin
  for i in 1 .. 100000 loop
    declare
      v_date date default null;
    begin
      v_date := sysdate;
    end;
  end loop;
end;
/

exec runstats_pkg.rs_middle;

begin
  for i in 1 .. 100000 loop
    declare
      v_date date default null;
    begin
      select sysdate
      into v_date
      from dual;
    end;
  end loop;
end;
/

exec runstats_pkg.rs_stop(100);

The results prove the point nicely:

Run1 ran in 18 hsecs
Run2 ran in 225 hsecs
run 1 ran in 8% of the time

 Name                               Run1        Run2          Diff
STAT...recursive cpu usage            0         174           174
STAT...CPU used when call star       18         222           204
STAT...DB time                       19         224           205
STAT...CPU used by this sessio       16         222           206
STAT...Elapsed Time                  18         225           207
STAT...session cursor cache hi        5      34,468        34,463
STAT...session uga memory        65,488           0       -65,488
STAT...opened cursors cumulati        5     100,005       100,000
STAT...calls to get snapshot s        1     100,001       100,000
STAT...execute count                  5     100,005       100,000
STAT...recursive calls                1     100,003       100,002
STAT...session pga memory       131,072           0      -131,072

Run1 latches total versus runs -- difference and pct
Run1        Run2    Diff       Pct
606        829     223    73.10%

Run 1 (without DUAL) used less CPU and wall clock time, over 10 times less which is a significant speedup. The difference in latches is not as big as I expected it to be.

This anti-pattern, 'needless select from dual' is so common, that Oracle actually changed the DUAL table in Oracle 10g. Before then, it was a real table that actually had a real block on disk, and selecting from it resulted in real I/O from disk or the buffer cache. In 10g Oracle introduced FAST_DUAL which is a completely virtual table, and selecting from it incurs no I/O at all. If you have an Oracle 9i instance to hand, try running the benchmark above, and the results should show an even bigger impact on using DUAL when you don't need to.