2

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

Improve this question

1 Answer 1

Reset to default
4

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;
Improve this answer
3
  • 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
    Commented Jul 30, 2012 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
    Commented Jul 30, 2012 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
    Commented Jul 31, 2012 at 12:08

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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