I have lots of these kinds of select and i want to change them so they use index.

select *
from t
where (x = l_some_variable OR (x is null and l_some_variable is null));

So i want to create a function-based index.

create index t_idx on t(nvl(x,-1));

My query become:

select *
from t
where nvl(x,-1) = nvl(l_some_variable,-1);


Writing this way, my select never use the function-index index.

SQL> set autotrace traceonly;
SQL> select *
2 from t
3 where nvl(x,-1) = nvl(null,-1);

103 rows selected.


Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 TABLE ACCESS (FULL) OF 'T'



scenario 1: all record have x record null;
select *
from t
where nvl(x,-1) = nvl(null,-1);

and

select *
from t
where nvl(x,-1) = nvl(100,-1);

no index use

scenario 2: all record are null exept 1 record that the value is 100

select *
from t
where nvl(x,-1) = nvl(null,-1);

and

select *
from t
where nvl(x,-1) = nvl(100,-1);

no index use


Even if i put a hint, it does not use the index.

What do i do wrong?