I have created a function based composite index using substr function. The query doesn't use the index. I am not sure wether substr can be used in a function based index and can a function based index be composite ??
From an Article
With standard indexing, if a column is involved in a function (for example, substr, trunc, etc.), the index is ignored. This results in full table scans and decreased performance. In 8i you have the ability to index a column with a function. This may be a built-in function or a user created function. The only caveat is the function must be deterministic (that is, given the same input it will always produce the same output). To enable this feature, the index owner must have the query rewrite the privilege, and set the following in your init.ora:
This done, let’s say you commonly query col1 of table1 with the statement:
select col1,col2,col3 from table1
The function to_char would cause any indexes on col1 to be ignored resulting in a full table scan in your explain plan. Now you can create an index:
create index table1_2_char on table1
After analyzing the table, an explain plan would look like Table A. The rule regarding data cardinality and index versus full scans still holds, but the performance gains you can realize can be amazing.
Table A: An explain plan
Query Cost Cardinality
Select statement choos 15 720
table access by index rowid table1 15 720
index range scan table1_idx analyzed 4 720
Note: it has been discovered that an index involving the trunc function will never be used, but a deterministic user created function that returns the trunc will work fine. This is an Oracle bug that they are aware of and plan to fix.
Attitude:Attack every problem with enthusiasam ...as if your survival depends upon it
In order to use function based index as specified in this thread, the table will have to be analyzed first, and analyzed regularly. So function based index carries certain amount of maintenance requirement.