-
function-based index
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?
-
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.,
Reddy,Sam
-
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'
-
is the index analyzed?
does your user have "query rewrite" system privilege granted (not through a role)?
-
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.
Jurij Modic
ASCII a stupid question, get a stupid ANSI
24 hours in a day .... 24 beer in a case .... coincidence?
-
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.
---------------
-
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!
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|