![](//teradataforum.com/imgs/ptdfw1.gif) |
![](//teradataforum.com/imgs/ptdfw2.gif) |
Archives of the TeradataForum
Message Posted: Wed, 14 Dec 2011 @ 08:33:36 GMT
Subj: | | Re: Tables which don't have stats |
|
From: | | Minakshi Swami |
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
)
| |