Secondary index degraded query performance

Hi All,

I have problem with index I just created, it has degraded one of my query execution time.

There was a requirement to create two indexes on a table, one index on "Posting Date" and "From Date". Before creating these indexes I have run 4 test cases where all test cases were taking 20-25 minutes (all the test cases have selection condition on any one of the files Posting date or From Date). Now I have created 2 indexes as required and repeated the same test cases to check the query performance.

In all 3 Test cases the performance improved drastically, query executed with in 10 seconds as compared to 20-25 minutes before index. But the problem is with 4th test case where the performance is degraded, now the query is taking 60-65 minutes when compared to 20-25 minutes before indexes were created.

I did performance trace for all 4 test cases and found that all of them are using the index Index i created.

Now how can I improve the performance of the 4th test case query.

Thanks,

Chindam Praveen Kumar.