Archives of the TeradataForum

Message Posted: Thu, 06 Mar 2008 @ 14:08:46 GMT

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

Yes, I've seen the case where current stats gives a worse access path than no stats. It is a bug. Good, current information should never cause the optimizer to make a less optimal access path choice than with no stats.

Please open an incident. It could be that the extra stats cause different code to referenced that could be improved. It'll never get fixed if it isn't identified.

You do refer to it as a single column stat so it is not the case, but if you have multi-column stats collected and one of the columns has nulls, unfortunately the optimizer will see this as 1 value and potentially produce a less than optimal access path. Example two column multi- stats.....one column has 1 million unique values, 1 million nulls, other column has 1 million values and 1 million nulls as in

     Col1      col2
     Abcd      abcd
     1234      null
     233434    null
     Null      1234
     Nul       14545
     Etc (you get the idea)

The optimizer will think there is only 2 distinct values for the column combination. If there is the potential for many nulls in a column grouping, DO NOT collect multi-column stats. (this may already be corrected in TD 12 - but I don't remember)

Ruth

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