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?