Archives of the TeradataForum

Message Posted: Fri, 30 Jan 2015 @ 16:33:32 GMT

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

That also misses sampled stats. The maxvaluelength are specific values we were given by the GSC to solve bad optimizer estimates generated by the default stats length and only apply to 14.0 and later. We collect sampled stats on about 30% of the tables we have that exceed 5 terabytes or so in size.

You can exclude any column where either samplesizepct or maxvaluelength is not null and use this query to pick up those regenerate statements in 14.x:

     SELECT 'Collect stats '
     ||
     CASE WHEN samplesizepct IS NOT NULL THEN 'using sample '
     ELSE ''
     END
     ||
     CASE WHEN samplesizepct IS NOT NULL
     AND MAXVALUELENGTH IS NOT NULL THEN ' and '
     ELSE
     CASE WHEN samplesizepct IS NULL THEN ' Using '
     ELSE ''
     END
     END
     ||
     CASE
     WHEN MAXVALUELENGTH IS NOT NULL THEN ' Maxvaluelength ' || TRIM(CAST(
     MAXVALUELENGTH AS CHAR(5)))
     ELSE ' '
     END
     ||
     /* commented out index - recommendation is to collect on the column,
     rather than on the index  R. Briggs
     CASE WHEN indexnumber IS NOT NULL THEN ' index '
     ELSE ' column '
     END
     */
     ' column '
     || ' ( ' || TRIM(ColumnName)||' ) ON '|| TRIM(DatabaseName) || '.' ||
     TRIM(TABLENAME) ||';' AS "Stats"
     FROM dbc.statsv
     WHERE columnname IS NOT NULL
     AND (MAXVALUELENGTH IS NOT NULL
     or samplesizepct IS NOT NULL)
     ORDER BY DatabaseName, TABLENAME, ColumnName;

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