 |
 |
Archives of the TeradataForum
Message Posted: Fri, 20 May 2005 @ 12:52:26 GMT
| Subj: | | Re: Collect stats and DBC.indexes.IndexStatistics |
| |
| From: | | Dieter Noeth |
Robert M Line wrote:
| | What I am trying to do is to determine where I have indexes without stats and run stats on those indexes. Problem is that I cannot
determine which indexes have stats and which ones do not. Any help would be greatly appreciated. | |
| | I have run Dieter's collect stats script that shows when stats where last run but the stats type shows an 'I' which I am assuming is an
index is not changing in volume as it should. | |
This should work, at least it did on various test cases :-)
Dieter
/***
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
)
;
| |
