I have a query where I am using the to_date() function in the where clause, due to that the index is not being utilized as it is a function and preventing the optimizer to use indexes on that column.
Is it good to create a function based index?
Wrong forum! This question belongs on the SQL and PL/SQL forum.
Depending on the selectivity of the index Oracle may not use it even if you have an index. You will have to test it.
If you are using Oracle 11 you could add a virtual column that performs the TO_DATE and then modify your query to use the new virtual column. See this Oracle-base article for an example of using virtual columns.
If the above does not answer your question
1. Create a new thread on the SQL and PL/SQL forum
2. Edit this question to add a link to the new question on the other forum
3. Mark this question ANSWERED so people will follow up in the other forum