create table t_count(c1 varchar(10), n1 number);

insert into t_count select rownum, rownum from dual connect by rownum <=100;

insert into t_count values (null, 1000);

insert into t_count values (1000, null);

create index indx_count_01 on t_count(c1);

create index indx_count_02 on t_count(n1);

stong11g> select * from t_count where c1 is null;

C1                 N1

---------- ----------

1000

Execution Plan

----------------------------------------------------------

Plan hash value: 3914636508

-----------------------------------------------------------------------------

| Id  | Operation         | Name    | Rows  | Bytes | Cost (%CPU)| Time     |

-----------------------------------------------------------------------------

|   0 | SELECT STATEMENT  |         |     1 |    20 |     3   (0)| 00:00:01 |

|*  1 |  TABLE ACCESS FULL| T_COUNT |     1 |    20 |     3   (0)| 00:00:01 |

-----------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

1 - filter("C1" IS NULL)

Note

-----

- dynamic sampling used for this statement

Statistics

----------------------------------------------------------

5  recursive calls

0  db block gets

16  consistent gets

0  physical reads

0  redo size

458  bytes sent via SQL*Net to client

381  bytes received via SQL*Net from client

2  SQL*Net roundtrips to/from client

0  sorts (memory)

0  sorts (disk)

1  rows processed

stong11g> select * from t_count where n1 is null;

C1                 N1

---------- ----------

1000

Execution Plan

----------------------------------------------------------

Plan hash value: 3914636508

-----------------------------------------------------------------------------

| Id  | Operation         | Name    | Rows  | Bytes | Cost (%CPU)| Time     |

-----------------------------------------------------------------------------

|   0 | SELECT STATEMENT  |         |     1 |    20 |     3   (0)| 00:00:01 |

|*  1 |  TABLE ACCESS FULL| T_COUNT |     1 |    20 |     3   (0)| 00:00:01 |

-----------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

1 - filter("N1" IS NULL)

Note

-----

- dynamic sampling used for this statement

Statistics

----------------------------------------------------------

5  recursive calls

0  db block gets

15  consistent gets

0  physical reads

0  redo size

460  bytes sent via SQL*Net to client

381  bytes received via SQL*Net from client

2  SQL*Net roundtrips to/from client

0  sorts (memory)

0  sorts (disk)

1  rows processed