
Archives of the TeradataForumMessage Posted: Thu, 03 Mar 2011 @ 14:00:18 GMT
Hope this Helps Teradata 12 includes statistics collection enhancements to better identify rows whose multicolumns contain partial NULLs, as opposed to all NULLs. In previous releases there was no way to make this determination, which resulted in all of the all NULL and partial NULL values counting as one distinct value. That could lead to skewed redistributions when some rows had all NULLs and others had partial NULLs. Because the optimizer no longer considers partial and all NULLs as the same, not only are redistributions more even, but better join costing and more accurate aggregations on those columns can also be performed. This distinction between full and partial NULLs is a result of the changes made to Interval 0 of a Teradata 12 statistics histogram. An exist ing field and a new field help the optimizer make this differentiation: * NumNulls. A count of all rows that have NULL on any column in the statistic * AllNulls. A count of rows that have NULL on all the columns of the statistic
One aspect of this change is that the database assumes each row reflects a distinct value when its multicolumn values are partially NULL, even when the nonNULL portions are the same. For that reason, if a multicolumn statistic includes a column with a high number of NULLs, you might want to consider removing that column from the statistic to make the statistic more usable. Below is the actual formula used by the optimizer for determining the number of distinct values in the multicolumn statistic: # Distinct values = # of distinct values from rows with no NULLs + (1 if AllNulls > 0 else 0) + # of rows with partial NULL. To help clarify this, consider a table that includes two columns (Region and State), as illustrated in table 2. Assume that multicolumn statistics have been collected on these two columns. In the rightmost column is text indicating how the number of distinct values for the statistics was changed by the contents of those two columns in that particular row. Thanks Machha
 
 
Copyright 2016  All Rights Reserved  
Last Modified: 15 Jun 2023  