We run a Test and Prod instance that were set-up identical
and are loaded with the same data. The problem is a query
that processes differently in the two environments. An index
was set-up in both environments to support the query. In one
enviroment it works as intended. In the other environment
the query still invokes a Full Table Scan and performance
is poor. This seems to be a consistent problem with other tables
and indexes as well. Any ideas?
1. Check if the compute statistics was run.
2. If it was then sometimes based on statistics even if there are indexes Oracle will do full tablescans which will be slower. Drop the statistics.
3. It the statistics were not run then, compute the statistics.