Archives of the TeradataForum

Message Posted: Thu, 02 Dec 2004 @ 16:44:56 GMT

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

Nazy,

Collecting stats, in general, will always help in getting good plan if the estimation made in steps is close to actual. There are many other internal estimated parameters like values, rows per values in spool estimations(not visible in explain) which too has to be accurate to determine skew, best join plan etc.

But there are many factors which prevent optimizer from accurately estimating spool size, skew detection etc....like complex functions or operators in join conditions. Sometimes skewness on columns is visible only after one level join is completed, when the table level info is lost. All these pose difficulty in estimations, even if all the stats are collected.

BTW, if you are getting good plan without stats, I would consider that you are lucky!. But collecting stats didn't get good estimates & that is why the bad plan. Please check the estimations Vs actual in dbql or PMON, you should notice some notable difference.

Thanks/Regards

Ranjan Priyadarshi
DBA, GEDW

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