Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I cannot figure out why the following is not working as I expect it :

CREATE TABLE table1(table1_id INT NOT NULL PRIMARY KEY,
      col1 INT NOT NULL,
      val1 VARCHAR(50));
CREATE INDEX IDX_table1_fnct ON table1(CASE WHEN col1 = 3 THEN val1 ELSE NULL END);

SELECT COUNT(val1) WHERE col1 = 3 ; shows full scan

I'd expect new index to be used for such queries, but it's not even if I try to force it with INDEX hint... Could anyone clarify why it works this way (I'm using Oracle 10 if that matters)?

Thank you

share|improve this question
add comment

1 Answer 1

up vote 4 down vote accepted

You can logically see that the index would narrow the scope of the count, but the database can't make the same determination. To use a function based index you need to use the function itself in your query. Assuming the data is such that the index will be useful, something like this should cause it to be used:

SELECT COUNT(val1) WHERE CASE WHEN col1 = 3 THEN val1 ELSE NULL END IS NOT NULL;
share|improve this answer
    
Thanks, but it still doesn't work exactly as you say : COUNT(val1) - still full scan, but count(*) index fast full scan... select val1... also ends up with full scan... –  a1ex07 Jul 30 '12 at 19:37
    
Finally I made it fast full scan , SELECT COUNT(WHEN col1 = 3 THEN val1 ELSE NULL END) WHERE CASE WHEN col1 = 3 THEN val1 ELSE NULL END IS NOT NULL;. Thank you again for hint. –  a1ex07 Jul 30 '12 at 20:39
    
Nice followup work. As you have already discovered, your modification will allow it to avoid having to look at the table at all as it can get everything it needs from the index. –  Leigh Riffel Jul 31 '12 at 12:08
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.