that is not a not null constraint though - it is a check constraint. It is NOT AS GOOD as a not null constraint!!!!
ops$tkyte%ORA11GR2> create table t1 (x int, y char(200), constraint x_not_null check (x is not null) );
Table created.
ops$tkyte%ORA11GR2> exec dbms_stats.set_table_stats( user, 'T1', numrows => 100000 );
PL/SQL procedure successfully completed.
ops$tkyte%ORA11GR2> create table t2 (x int NOT NULL, y char(200) );
Table created.
ops$tkyte%ORA11GR2> exec dbms_stats.set_table_stats( user, 'T2', numrows => 100000 );
PL/SQL procedure successfully completed.
ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> create index t1_idx on t1(x);
Index created.
ops$tkyte%ORA11GR2> create index t2_idx on t2(x);
Index created.
ops$tkyte%ORA11GR2> set autotrace traceonly explain
ops$tkyte%ORA11GR2> select count(*) from t1;
Execution Plan
----------------------------------------------------------
Plan hash value: 3724264953
-------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 29 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | TABLE ACCESS FULL| T1 | 100K| 29 (0)| 00:00:01 |
-------------------------------------------------------------------
ops$tkyte%ORA11GR2> select count(*) from t2;
Execution Plan
----------------------------------------------------------
Plan hash value: 1058009622
-------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 0 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | <b> INDEX FULL SCAN| </b>T2_IDX | 100K| 0 (0)| 00:00:01 |
-------------------------------------------------------------------
ops$tkyte%ORA11GR2> select /*+ index( t1 t1_idx ) */ count(*) from t1;
Execution Plan
----------------------------------------------------------
Plan hash value: 3724264953
-------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 29 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | TABLE ACCESS FULL| T1 | 100K| 29 (0)| 00:00:01 |
-------------------------------------------------------------------
ops$tkyte%ORA11GR2> set autotrace off
the optimizer will not understand in this case that X is not null, use the NOT NULL constraint.