| Function Based Index is not being used [message #544537] |
Wed, 22 February 2012 05:43  |
 |
orapratap
Messages: 134
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
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 #544604 is a reply to message #544540] |
Wed, 22 February 2012 21:53  |
|
orapratap
Messages: 134
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 #544657 is a reply to message #544608] |
Thu, 23 February 2012 03:43 |
cookiemonster
Messages: 13962
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
Just to expand on that, this function:
(case when status<>'C' then null end)
So this:
where(case when status<>'C' then null end)<>'C'
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 |
Roachcoach
Messages: 1576
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.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
