I gave a presentation at the Teradata Partners Conference last week on the fundamentals of collecting statistics, where I touched briefly on the rules behind confidence levels. I’m using this article to go into further depth, and offer you more examples of how these confidence levels come about. If you look at query plans much, I’m confident you’ll find something of interest in this article.

Cached plans are good. For repetitive queries, cached plans remove the overhead of parsing and optimizing, and this helps short work perform more consistently. But sometimes cached plans, being general-purpose and one-size-fits-all, are not able to take advantage of specific optimizations that could help selective executions of the query if only the optimizer could see those parameterized SQL values. New in Teradata 12, the optimizer has the opportunity, under specific conditions, to peek at values in a parameterized SQL request.

Have you ever had what looks like a great query plan turn into life in the slow lane at run time?