where NVL(item1,'ggg')

where NVL(item1,'ggg') = 'ggg'

where (item1 = 'ggg' or item1 is null and 'ggg' = 'ggg')

SQL> create table t1 as select * from all_objects;

Table created.

SQL> select nullable from user_tab_columns where table_name='T1' and column_name='OBJECT_ID';

N
-
N

SQL> create index i_t1_object_id on t1 (object_id);

Index created.
SQL> exec dbms_stats.gather_table_stats(user, 't1', cascade=>true);

PL/SQL procedure successfully completed.

SQL> set autotrace traceonly explain
SQL> select * from t1 where nvl(object_id, -1)=1;

Execution Plan
----------------------------------------------------------
Plan hash value: 3617692013

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |    93 |   150   (1)| 00:00:02 |
|*  1 |  TABLE ACCESS FULL| T1   |     1 |    93 |   150   (1)| 00:00:02 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(NVL("OBJECT_ID",(-1))=1)

SQL> select * from t1 where (object_id=1 or object_id is null or -1=-1);

Execution Plan
----------------------------------------------------------
Plan hash value: 3617692013

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      | 49181 |  4466K|   150   (1)| 00:00:02 |
|   1 |  TABLE ACCESS FULL| T1   | 49181 |  4466K|   150   (1)| 00:00:02 |
--------------------------------------------------------------------------

SQL> select * from t1 where (object_id=1 or object_id is null);

Execution Plan
----------------------------------------------------------
Plan hash value: 852682354

----------------------------------------------------------------------------------------------
| Id  | Operation                   | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                |     1 |    93 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T1             |     1 |    93 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | I_T1_OBJECT_ID |     1 |       |     1   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("OBJECT_ID"=1)

SQL> alter table t1 modify object_id null;

Table altered.

SQL> select * from t1 where (object_id=1 or object_id is null);

Execution Plan
----------------------------------------------------------
Plan hash value: 3617692013

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |    93 |   150   (1)| 00:00:02 |
|*  1 |  TABLE ACCESS FULL| T1   |     1 |    93 |   150   (1)| 00:00:02 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("OBJECT_ID"=1 OR "OBJECT_ID" IS NULL)

(variable_ is not null and item1=variable_ or variable_ is null and item1 is null)