Why is Explain Plan different between environments
The optimizer is using a different explain plan for the same query between my staging environment and the production environment. Staging is using an index while production uses a full table scan
All of the parameters (validated from v$parameter) are the same in the 2 environments. The statistics for the table were calculated on staging and imported to production so those are the same. As far as I can tell there is no logic reason for the difference so why are the plans different?
The table is partitioned (range by date) and if the query is executed on production against last months partition, the plan will use an index. Executed for the current month will use a full table scan (staging uses the index for both queries).
I have re-ran statistic (I am using global stats) in production but the plan did not change. What should I do to get production using the correct plan?
Thanks in advance.