I totally agree with your assesments. There's just one more flavor, which I'd like to add to the mix: That's when the underlying data changes. Optimizers (on w

I totally agree with your assesments. There's just one more flavor, which I'd like to add to the mix: That's when the underlying data changes. Optimizers (on which I agree with you are usually much better than any developer / DBA in assessing the optimal path to execute a query). They do rely on statistcs, however, which are not automatically updated. This is also reasonable since it would be much too expensive to run real time updates on statistics, while the database is in use. A heavily transaction oriented system (which are still a lot of relational database implementations) would be killed by such a requirement. If those indexes are stale, however, this can totally kill the optimizer's ability to determine a good plan. I've seen very, very few examples when a optimized plan sucks, when it's based on correct statistics. And I agree that hints are a a good way to fix this. I'd make this conditional that they always need to be reviewed with a new version of the underlying database engine.