Archives of the TeradataForum

Message Posted: Thu, 08 Jul 2004 @ 16:34:19 GMT

	<Prev	Next>		<<First	<Prev	Next>	Last>>

Kumaran,

The Minimum Explain Confidence Level option causes TDQM to bypass any query step (it does not bypass the entire query, only that step) from consideration if the step's confidence level is below the specified confidence level.

Using your example below, if you checked Confidence Level Medium, then any query step in the plan that specified no confidence or low confidence, that entire step would be bypassed by the rule. Only the estimates in the other steps would be summed in assessing the total estimated processing time, or total estimated row count. If a query had only one step and that step had no or low confidence, then that entire query would be ineligible for consideration by that rule.

In your example, only steps 2 and 4 will be considered. The rows for those two steps will be summed, as will the estimated times. These sums must both exceed the thresholds you set, because you AND-ed them together in the rule definition.

So your statement "Reject if t2+t4 >= T" is only half correct. To be complete, it should be stated "Reject if t2+t4>=T AND n2 +n4>=N".

I'd be careful using the Minimum Explain Confidence Level option, because it could result in candidate queries (potentially long-running ones, for example) from being properly screened by TDQM. Low or no confidence steps are not uncommon near the bottom of the plan in multi-step queries that are composed of many, many steps. And if statistics have not been thoroughly collected, low or no confidence steps are even more likely. Queries that have steps with no or low confidence may not have been "optimally" optimized due to lack of stats, and may in fact be just the queries you want to apply TDQM rules against the most. Yet setting the Confidence Level has just the opposite result, it exempts those steps from consideration.

Thanks, --Carrie

	<Prev	Next>		<<First	<Prev	Next>	Last>>