A collection of tutorials, code and tools to help you get better using Oracle
14 June 2011
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.