Home » RDBMS Server » Performance Tuning » Function Based Index is not being used (Oracle 10.2.0.4)
Function Based Index is not being used [message #544537] Wed, 22 February 2012 05:43 Go to next message
orapratap
Messages: 113
Registered: November 2011
Location: Canada
Senior Member
Hello

I have a table which has 4M records

This table has a query where one of the condition is
AND STATUS <> 'C'

Now the data is as following


select count(*) record_count, status from new_business group by status;

record_count		status
4298025			C
15			N
13			Q
122			S



I want to know if following index would be useful in this case while the condition in where clause is
"AND STATUS <> 'C'"


create index nb_index_1 on new_business(case when status in('N','Q','S') then 1 else NULL end);
Or
create index nb_index_1 on new_business(case when status ='N' then 'N' when status='Q' then 'Q' when status='S' then 'S' else NULL end);


I tried it on a sample table but the index is simply not picked up even when hinted

following are the db level settings
query_rewrite_enabled                string      TRUE
query_rewrite_integrity              string      enforced


I tried it 'query_rewrite_integrity' with 'trusted' as well but no luck!

Please suggest

Thanks and Regards
OraPratap
Re: Function Based Index is not being used [message #544539 is a reply to message #544537] Wed, 22 February 2012 05:49 Go to previous messageGo to next message
cookiemonster
Messages: 10961
Registered: September 2008
Location: Rainy Manchester
Senior Member
If the function in the index is not in the where clause, why would you expect it to be used?
Re: Function Based Index is not being used [message #544540 is a reply to message #544537] Wed, 22 February 2012 05:51 Go to previous messageGo to next message
Michel Cadot
Messages: 59143
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
I want to know if following index would be useful in this case while the condition in where clause is


No.

Regards
Michel
Re: Function Based Index is not being used [message #544604 is a reply to message #544540] Wed, 22 February 2012 21:53 Go to previous messageGo to next message
orapratap
Messages: 113
Registered: November 2011
Location: Canada
Senior Member
Hello Michel and cookiemonster

I have changed the index definition and structire of the query

Now the Index is being used but producing wrong results



SELECT COUNT(*),status FROM new_business group by status;

  COUNT(*) STATUS
---------- -------
     40136 C
         1 N
         1 Q
         1 S

create index nb_indx on new_business (case when status<>'C' then null end);

select /*+ index(new_business pi2) gather_plan_statistics */ * 
from new_business where(case when status<>'C' then null end)<>'C';

no rows selected

select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));
-----------------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name		| Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-----------------------------------------------------------------------------------------------------------
|   1 |  TABLE ACCESS BY INDEX ROWID| NEW_BUSINESS	|      1 |   2007 |      0 |00:00:00.01 |       1 |
|*  2 |   INDEX FULL SCAN           | NB_INDX 		|      1 |      1 |      0 |00:00:00.01 |       1 |
-----------------------------------------------------------------------------------------------------------


Thanks and Regards
OraPratap

[Updated on: Wed, 22 February 2012 23:19] by Moderator

Report message to a moderator

Re: Function Based Index is not being used [message #544606 is a reply to message #544604] Wed, 22 February 2012 22:09 Go to previous messageGo to next message
BlackSwan
Messages: 22790
Registered: January 2009
Senior Member
>Now the Index is being used but producing wrong results
post what you deem the correct results should be
Re: Function Based Index is not being used [message #544608 is a reply to message #544604] Wed, 22 February 2012 23:24 Go to previous messageGo to next message
Michel Cadot
Messages: 59143
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
where(case when status<>'C' then null end)<>'C';


This:
1/ implies FTS
2/ Can NEVER return a single row, that is can NEVER return the expected result.

Gather statistics on your index, I bet there is nothing (no leaf) in it.

Regards
Michel
Re: Function Based Index is not being used [message #544657 is a reply to message #544608] Thu, 23 February 2012 03:43 Go to previous messageGo to next message
cookiemonster
Messages: 10961
Registered: September 2008
Location: Rainy Manchester
Senior Member
Just to expand on that, this function:
(case when status<>'C' then null end)
can return exactly one value: null

So this:
where(case when status<>'C' then null end)<>'C'
Can never be true, because null is not equal to anything and it's not not equal to anything.

Your case needs an else.
Re: Function Based Index is not being used [message #544660 is a reply to message #544608] Thu, 23 February 2012 03:45 Go to previous message
Roachcoach
Messages: 1203
Registered: May 2010
Location: UK
Senior Member
You need something like this:

create index fbi on t(case when flag_col !='a' then 1 else null end)
/

select * from t where case when flag_col!='a' then 1 else null end =1
/



Assuming your representation is accurate in terms of rows/cardinality, it's not a big table though (unless it has some massive columns), it may be worth (depending on design/existing DDL structures) experimenting with a bitmap index?

Function based selective indexes can be powerful, but their application is very specific.
Previous Topic: how to find top query running on a table
Next Topic: Issue with bulk collect over a db link
Goto Forum:
  


Current Time: Sat Sep 20 01:23:03 CDT 2014

Total time taken to generate the page: 0.11999 seconds