Connected to Oracle9i Enterprise Edition Release 9.2.0.7.0 
Connected as mni
 
SQL> 
SQL> create table t_mni (
  2    id         number(10),
  3    faux_nr    varchar2(10)
  4  )
  5  /
 
Table created
SQL> insert into t_mni(id, faux_nr) Values (1, '10')
  2  /
 
1 row inserted
SQL> insert into t_mni(id, faux_nr) Values (2, 'A')
  2  /
 
1 row inserted
SQL> commit
  2  /
 
Commit complete
SQL> select to_number(faux_nr)
  2    from t_mni
  3  /
 
select to_number(faux_nr)
  from t_mni
 
ORA-01722: Nombre non valide
SQL> select count(*)
  2    from (
  3          select to_number(faux_nr)
  4            from t_mni
  5          )
  6  /
 
  COUNT(*)
----------
         2
 
SQL> 

select count(*) from (select to_number(faux_nr) from t_mni)

is the same as

select count(*) from (select null from t_mni);

(eg: count the number of rows)

which is of course the same as

select count(*) from t_mni;

Now, the optimizer knows that is really just counting rows - it 'knows' the columns in the select list (the projection) and not necessary.

counting a set is not a good thing to do unless your answer is to be "how many rows are in this set".


I see people try to 'benchmark' a query Q by:

select count(*) from (Q)

not realizing that the plan for Q by itself is almost certainly a lot different than the plan for "select count(*) from (q)" is. We can do things very differently.

ops$tkyte%ORA10GR2> exec dbms_stats.set_table_stats( user, 'T_MNI', numrows => 100000 );

PL/SQL procedure successfully completed.

ops$tkyte%ORA10GR2> set autotrace traceonly explain
ops$tkyte%ORA10GR2> select count(*)
  2    from (select to_number(faux_nr) from t_mni)
  3  /

Execution Plan
----------------------------------------------------------
Plan hash value: 1760092885

--------------------------------------------------------------------
| Id  | Operation          | Name  | Rows  | Cost (%CPU)| Time     |
--------------------------------------------------------------------
|   0 | SELECT STATEMENT   |       |     1 |     9  (12)| 00:00:01 |
|   1 |  SORT AGGREGATE    |       |     1 |            |          |
|   2 |   TABLE ACCESS FULL| T_MNI |   100K|     9  (12)| 00:00:01 |
--------------------------------------------------------------------

ops$tkyte%ORA10GR2> alter table t_mni add constraint t_mni_pk primary key(id);

Table altered.

ops$tkyte%ORA10GR2> select count(*)
  2    from (select to_number(faux_nr) from t_mni)
  3  /

Execution Plan
----------------------------------------------------------
Plan hash value: 2504573254

---------------------------------------------------------------------
| Id  | Operation        | Name     | Rows  | Cost (%CPU)| Time     |
---------------------------------------------------------------------
|   0 | SELECT STATEMENT |          |     1 |     1   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE  |          |     1 |            |          |
|   2 |   INDEX FULL SCAN| T_MNI_PK |   100K|     1   (0)| 00:00:01 |
---------------------------------------------------------------------

notice how after we add a primary key - the full scan of the table disappears and we answer the question using an index that doesn't even contain the column you referenced!!!!