DUMMY issues

I've just noticed in one of LinkedIn PL/SQL forums the following question:
- Which one is faster and why SELECT 1 FROM DUAL or SELECT ROWID FROM DUAL?

Yes, it's pretty basic question, but it's interesting enough to be covered. The answer is pretty simple - do not use ROWID at all. At some point Oracle introduced a notion of FAST DUAL (instead of a real table) exactly for performance reasons. Here is a quote from the doc:

*********************************
Beginning with Oracle Database 10g Release 1, logical I/O is not performed on the DUAL table when computing an expression that does not include the DUMMY column. This optimization is listed as FAST DUAL in the execution plan. If you SELECT the DUMMY column from DUAL, then this optimization does not take place and logical I/O occurs.
**********************************

It is obvious, that selecting of ROWID is conceptually the same as selecting of a real column, because it returns a physical property of a row. I understand that the impact may not be that great, but with the significant number of executions is can add up pretty quickly. Also, let's not confuse CBO more than it really needs. :-)

As a proof - explain plans (from dbms_xplan.display) below:

* explain plan for SELECT 1 FROM DUAL

------------------------------------------------------
| Id  | Operation        | Name | Rows  | Cost (%CPU)|
------------------------------------------------------
|   0 | SELECT STATEMENT |      |     1 |     2   (0)|
|   1 |  FAST DUAL       |      |     1 |     2   (0)|
------------------------------------------------------

* explain plan for SELECT ROWID FROM DUAL

---------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)|
---------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |     2 |     2   (0)|
|   1 |  TABLE ACCESS FULL| DUAL |     1 |     2 |     2   (0)|
---------------------------------------------------------------


* explain plan for SELECT DUMMY FROM DUAL

---------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)|
---------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |     2 |     2   (0)|
|   1 |  TABLE ACCESS FULL| DUAL |     1 |     2 |     2   (0)|
---------------------------------------------------------------

Summary:

• goal: we need to avoid unnessessary IO as much as we can
• key thought: do not use DUMMY or ROWID when selecting from DUAL

P.s. I decided to check how much will be the difference and run the following set of variations:

declare
    v_out_nr number;
begin
    for c in 1..10000 loop
       select 1 into v_out_nr from dual;
    end loop;
end; 

declare
    v_out_tx varchar2(1);
begin
    for c in 1..10000 loop
       select dummy into v_out_tx from dual;
    end loop;
end; 

declare
    v_out_rec rowid;
begin
    for c in 1..10000 loop
       select rowid into v_out_rec from dual;
    end loop;
end; 

declare
    v_out_nr number;
begin
    for c in 1..10000 loop
       -- Yes, I've see that too!
       select rownum into v_out_nr from dual; 
    end loop;
end; 

Here are results (an average of 5 runs):

• 1:                 0.88
• RowNum:    1.10
• Dummy:       1.31
• RowID:        1.39

A couple of interesting things:
1. ROWID is even slower than just DUMMY
2. RowNUM is still using FAST DUAL, but introduces extra step in the execution plan (COUNT), which slows it down