function-based index

[steeve123]

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?

[sreddy]

You have to read why my index not used doc from metalink/tom kyte site. Index will not be use on NULL columns. Oracle only use index if there is enough volume of data and stats for optimizer etc.,

[steeve123]

I tried tons of example, could someone show me an example that works.

i did this:

create table t
as
select * from all_users;


alter table t add x number;

update t set x = 1;
update t set x = 2 where username = 'SYS';
update t set x = NULL where username = 'SYSTEM';

SQL> select * from t where nvl(x,-1) = nvl(1,-1);
103 rows selected.

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


SQL> select * from t where nvl(x,-1) = nvl(2,-1);

USERNAME USER_ID CREATED X
------------------------------ ---------- --------- ----------
SYS 0 28-FEB-01 2

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



SQL> select * from t where nvl(x,-1) = nvl(NULL,-1);

USERNAME USER_ID CREATED X
------------------------------ ---------- --------- ----------
SYSTEM 5 28-FEB-01


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

[slimdave]

is the index analyzed?
does your user have "query rewrite" system privilege granted (not through a role)?

[jmodic]

Originally posted by sreddy
Index will not be use on NULL columns.

Well, that's (generaly speaking) not true, not even for normal B*Tree indexes and even less for function based indexes.

[Bhavin Hingu]

hi,

As far as I know,
Function based index only works with Cost based Optimizer and you have to set one init.ora parameter QUERY_REWRITE_ENABLE to TRUE. But even though You do it, There is no guarantee that Oracle Optimizer will definately use FBI (Function based Index) , according to their documentation.

Thanks,

Bhavin.

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

[steeve123]

i have cbo but not query_rewrite_enabled set to true, i did change it to true and IT WORKS!!!

THANKS MY QUERY USE THE FUNCTION INDEX!