I have a SQL query that I want to optimize. The query has execution time of ~ 1min 15 sec and an explain plan cost of 54,000. I created an index that I feel should improve performance. And indeed explain plan shows the cost reduction of 100 times! But the execution time has INCREASED to 5min. What is going on here?
That depends on the query, the table and the index.
The explain plan shows that the Oracle optimizer THINKS that using the index will be faster, but that might not always be the case. There might be some circumstances that the optimizer didn't see or the statistics could be outdated.
In this case: Don't use the index. Maybe think of another index, re-writing the query or some other solution.
For more help, please post more details on the query, table etc.