Oracle function based index is not used in query 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
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;