Duelling with DUAL


We all know DUAL - the table you need for that one row, one column answer. Its used extensively in our applications, but also the Oracle data dictionary, and implicitly by PL/SQL (when retrieving such things as SYSDATE and USER values).

Querying from DUAL (for reasons presumably only known to the Oracle internal people) seems to be more expensive than it needs to be. Under 8.1, we see the following behaviour:

SQL> set autotrace on statistics
SQL> select * from dual;

Statistics
----------------------------------------------------------
          4  db block gets
          1  consistent gets

most of the access actually being on the segment header block. Of course, this should typically not be a problem, but if you are hammering dual (for example, selecting sequence values from dual, or implicitly via PL/SQL), then these reads could take their toll on your system. I first came across this when using table API type of features in Designer - all of the PL/SQL generated tends to be very "generous" in its use of SYSDATE and the like.

So what happens when DUAL is an IOT ?

SQL> create table xdual ( dummy primary key ) organization index 
  2  as select dummy from dual;

Table created.

SQL> set autotrace on statistics
SQL> select * from xdual;

Statistics
----------------------------------------------------------
          4  db block gets
          2  consistent gets

which does not appear as good, but if we ANALYZE the table:

SQL> analyze table xdual estimate statistics;

Table analyzed.

SQL> select * from xdual;

Statistics
----------------------------------------------------------
          0  db block gets
          1  consistent gets

we get a considerable improvement. There is also a possibly even better enhancement (as suggested by Steve Adams) of DUAL being a view onto the virtual table X$DUAL

SQL> create view ydual as select dummy from x$dual;

View created.

SQL> select * from ydual;

Statistics
----------------------------------------------------------
          0  db block gets
          0  consistent gets

So should we replace the existing DUAL with a view on X$DUAL or an IOT? That is more debatable, since it really depends if you could get Oracle support to accept your requirements for it. There is of course nothing to stop you from having your version of DUAL (and leaving the supplied one alone), and ensuring that your application uses your version.

Interestingly, in 9i, the DUAL situation has improved somewhat:

SQL> select * from dual;

Statistics
----------------------------------------------------------
          2  db block gets
          1  consistent gets