SQL> select
   2     round (max (ratio),  2 ) "max",
   3     round (min (ratio),  2 ) "min",
   4     round (avg (ratio),  2 ) "avg",
   5     round (stddev (ratio),  2 ) "stddev"
   6   from
   7     (select t.owner, t.table_name, t.num_rows, i.num_rows,
   8      i.num_rows / t.num_rows *  100  ratio
   9      from dba_indexes i, dba_tables t
  10      where i.index_type <> 'CLUSTER'
  11      and t.owner = i.owner and t.table_name = i.table_name
  12      and t.num_rows > greatest (i.num_rows,  1000 ));

       max        min        avg     stddev
---------- ---------- ---------- ----------
        100            0        33 , 52        42 , 59 

SQL> create table sn as
   2   select rownum i, case when mod (rownum,  5 ) =  0  then null else rownum end j
   3   from dba_objects, dba_objects
   4   where rownum <=  1000000 ;

Table created.

SQL> create index sn_i on sn (coalesce (j, - 1 ));

Index created.

SQL> create bitmap index sn_bi on sn (j);

Index created.

SQL> select /*+ FULL(sn) */ * from sn where j is null;

 200000  rows selected.

Elapsed:  00 : 00 : 07 . 79 

Execution Plan
----------------------------------------------------------                      
    0       SELECT STATEMENT Optimizer=ALL_ROWS (Cost= 584  Card= 207040  Bytes= 5383040 )
    1      0    TABLE ACCESS (FULL) OF 'SN' (TABLE) (Cost= 584  Card= 207040  Bytes= 5383040 )

Statistics
----------------------------------------------------------                      
       15356   consistent gets                                                    

SQL> select /*+ INDEX(sn sn_bi) */ * from sn where j is null;

 200000  rows selected.

Elapsed:  00 : 00 : 07 . 73 

Execution Plan
----------------------------------------------------------                      
    0       SELECT STATEMENT Optimizer=ALL_ROWS (Cost= 1426  Card= 207040  Bytes= 5383040 )
    1      0    TABLE ACCESS (BY INDEX ROWID) OF 'SN' (TABLE) (Cost= 1426  Card= 207040  Bytes= 5383040 )
    2      1      BITMAP CONVERSION (TO ROWIDS)
    3      2        BITMAP INDEX (SINGLE VALUE) OF 'SN_BI' (INDEX (BITMAP))

Statistics
----------------------------------------------------------                      
       15285   consistent gets                                                    

SQL> select /*+ INDEX(sn sn_i) */ * from sn where coalesce (j, - 1 ) = - 1 ;

 200000  rows selected.

Elapsed:  00 : 00 : 08 . 15 

Execution Plan
----------------------------------------------------------                      
    0       SELECT STATEMENT Optimizer=ALL_ROWS (Cost= 4  Card= 207040  Bytes= 5383040 )
    1      0    TABLE ACCESS (BY INDEX ROWID) OF 'SN' (TABLE) (Cost= 4  Card= 207040  Bytes= 5383040 )
    2      1      INDEX (RANGE SCAN) OF 'SN_I' (INDEX) (Cost= 3  Card= 4143 )

Statistics
----------------------------------------------------------                      
       28967   consistent gets