Please stop (ab)using DUAL!

Update (2018-07-28) Lukas Eder highlighted via twitter that my comparison was invalid dues to some PL/SQL optimizations. I have updated the testcase, there is still significant improvement, just not as much as my previous test

Recently I’ve been noticing a lot of frequently called PL/SQL code that has structure similar to the following:


I really don’t know how people got into the habit of doing things this way, isn’t the following much simpler?

l_string := TO_CHAR(l_date, 'YYYY-MM-DD');

Does it really matter? Isn’t it just a coding preference? Let’s benchmark to see the difference:

SQL> declare
  2      l_string VARCHAR2(10);
  3      l_len NUMBER := 0;
  4      l_date DATE := SYSDATE;
  5  begin
  7      for loopy in 1..1000000
  8      loop
  9          SELECT TO_CHAR(SYSDATE, 'YYYY-MM-DD') into l_string FROM DUAL;
 10          l_len := l_len + LENGTH(l_string);
 11      end loop;
 12      dbms_output.put_line(l_len);
 13  end;
 14  /
PL/SQL procedure successfully completed.
Elapsed: 00:00:17.171

We executed the statement one million times in just over 17 seconds. Not too shabby. However let’s try the other way:

SQL> declare
  2      l_string VARCHAR2(10);
  3      l_len NUMBER := 0;
  4  begin
  5      for loopy in 1..1000000
  6      loop
  7          l_string := TO_CHAR(SYSDATE, 'YYYY-MM-DD');
  8          l_len := l_len + LENGTH(l_string);
  9      end loop;
 10      dbms_output.put_line(l_len);
 11  end;
 12  /
PL/SQL procedure successfully completed.
Elapsed: 00:00:02.082

Same amount of work, eight times faster. Why? Probably something to do with context switches between PL/SQL and SQL Engines, but don’t stress too much about that, just look at the figures.

Oracle Database CPU cycles are expensive, don’t waste them doing things inefficiently. Please stop abusing the DUAL table.

Leave a Comment

Your email address will not be published.