Archives of the TeradataForum

Message Posted: Wed, 14 Dec 2011 @ 08:33:36 GMT

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

Collecting statistics always builds a "histogram" data structure which is stored as a binary object in DBC.TVFields.FieldStatistics for single column or DBC.Indexes.IndexStatistics for multi-column.

     /***
     Check for missing stats on indexes.
     ***/

     SELECT
        DatabaseName,
        TableName,
        ColumnList
     FROM
       (
        SELECT
          d.databasename AS DatabaseName,
          t.tvmname AS TableName,

          /*** Build a column list for multi-column indexes up to 16 columns ***/
          MAX(CASE WHEN i.FieldPosition = 1 THEN TRIM(c.FieldName) ELSE ''
     END) ||
          MAX(CASE WHEN i.FieldPosition = 2 THEN ',' || TRIM(c.FieldName)
     ELSE '' END) ||
          MAX(CASE WHEN i.FieldPosition = 3 THEN ',' || TRIM(c.FieldName)
     ELSE '' END) ||
          MAX(CASE WHEN i.FieldPosition = 4 THEN ',' || TRIM(c.FieldName)
     ELSE '' END) ||
          MAX(CASE WHEN i.FieldPosition = 5 THEN ',' || TRIM(c.FieldName)
     ELSE '' END) ||
          MAX(CASE WHEN i.FieldPosition = 6 THEN ',' || TRIM(c.FieldName)
     ELSE '' END) ||
          MAX(CASE WHEN i.FieldPosition = 7 THEN ',' || TRIM(c.FieldName)
     ELSE '' END) ||
          MAX(CASE WHEN i.FieldPosition = 8 THEN ',' || TRIM(c.FieldName)
     ELSE '' END) ||
          MAX(CASE WHEN i.FieldPosition = 9 THEN ',' || TRIM(c.FieldName)
     ELSE '' END) ||
          MAX(CASE WHEN i.FieldPosition = 10 THEN ',' || TRIM(c.FieldName)
     ELSE '' END) ||
          MAX(CASE WHEN i.FieldPosition = 11 THEN ',' || TRIM(c.FieldName)
     ELSE '' END) ||
          MAX(CASE WHEN i.FieldPosition = 12 THEN ',' || TRIM(c.FieldName)
     ELSE '' END) ||
          MAX(CASE WHEN i.FieldPosition = 13 THEN ',' || TRIM(c.FieldName)
     ELSE '' END) ||
          MAX(CASE WHEN i.FieldPosition = 14 THEN ',' || TRIM(c.FieldName)
     ELSE '' END) ||
          MAX(CASE WHEN i.FieldPosition = 15 THEN ',' || TRIM(c.FieldName)
     ELSE '' END) ||
          MAX(CASE WHEN i.FieldPosition = 16 THEN ',' || TRIM(c.FieldName)
     ELSE '' END) ||
          MAX(CASE WHEN i.FieldPosition > 16  THEN ',...' ELSE '' END) AS ColumnList,

          MAX(i.LastAlterTimestamp) AS LastAlterTimestamp,

          COUNT(*) AS ColumnCount,

          /*** NULL if there are no stats ***/
          MAX(SUBSTR(i.IndexStatistics, 1, 80)) AS Stats

        FROM
          dbc.dbase d
        JOIN dbc.tvm t
          ON d.databaseid = t.databaseid
        JOIN dbc.tvfields c
          ON t.tvmid = c.tableid
        JOIN dbc.Indexes i
          ON c.tableid = i.tableid
          AND c.fieldid = i.fieldid
        GROUP BY
          d.DatabaseName,
          t.Tvmname,
          i.IndexNumber
       ) dt

     /*** If multi-column stats were collected before the index was created
           those stats are NOT stored within that index definition.
           Instead there are two rows with the same set of columns, but
           different IndexNumbers and only the 'M' row has stats.
           That special case is checked here.
     ***/
     GROUP BY 1,2,3
     HAVING MAX(Stats) IS NULL

     /*** Stats on single column indexes are stored within tvfields ***/
     AND (DatabaseName, TableName, ColumnList) NOT IN
       (SELECT d.databasename, t.tvmname, c.fieldname
        FROM
          dbc.dbase d
        JOIN dbc.tvm t
          ON d.databaseid = t.databaseid
        JOIN dbc.tvfields c
          ON t.tvmid = c.tableid
        WHERE c.fieldstatistics IS NOT NULL
       )

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