DUAL behavior change

While reading the book Expert Oracle Database Architecture, I noticed a very important bit of information about the DUAL table. In 10g, Oracle does not do a full table scan on DUAL if it does not need to. This is not the case in versions prior to 10g. To illustrate:

In 9.2:

SQL> set autotrace on
SQL> select sysdate from dual;

SYSDATE
---------
09-NOV-05


Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1)
   1    0   TABLE ACCESS (FULL) OF 'DUAL' (Cost=2 Card=1)




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

Notice the TABLE ACCESS (FULL) OF ‘DUAL’ and the 3 consistent gets

In Oracle 10g XE:

SQL> set autotrace on
SQL> select sysdate from dual;

SYSDATE
---------
09-NOV-05


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
        415  bytes sent via SQL*Net to client
        380  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

Notice that in 10g, there is no consistent gets and no full table scan. select sysdate from dual is treated as if I was calling a function directly. This is a very nice enhancement. As a side note, notice the enhancement to the autotrace results in 10g.

Possibly related:

Tagged , | Post a Comment