What is the SQL Optimizer's decision when two or more execution plans have the same cost?
Two questions: When building new plans, What is the optimizer's behavior when it must decide between plans with the same cost?
Also, Why would the optimizer build and choose a bad plan for a SQL statement when there are current plans in memory that execute satisfactorily?
In the attached example for a particular SQL statement, the optimizer is computing and using a new SQL plan (plan hash 636462009) with the same cost as a previous one (394), but this new plan takes much more longer to complete (higher elapsed time) than the previous one. Why didn't the optimizer use the plan with the shorter elapsed time (plan hash =4151688951), and decided on a worst plan (but with same cost as a previous one)?