Archives of the TeradataForum

Message Posted: Mon, 25 Sep 2006 @ 22:49:26 GMT

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

There's no difference between collecting stats on a single column versus an index containing that single column other than the syntax. The COLLECT STATS processing and the resulting dictionary info will be the same either way. Same is true for collecting based on multiple columns versus an index containing those same columns. If you collect stats on multiple columns and don't have a matching index, Teradata in effect creates a dummy index definition. If an index does not have an index name, it can still be referenced in DDL (as an index) by listing the included columns in parentheses.

The following SQL needs to be updated (doesn't allow for PPI PARTITION stats, for example) but I've used something like this in the past:

     /* For single column, stats are in TVFields table: */

     select  'COLLECT STATISTICS ON ' || trim(d.Databasename ) || '.' ||
     trim(t.TVMName) || ' COLUMN ' ||  trim(f.Fieldname) ||';' (CHAR(255))
     from dbc.tvfields f, dbc.tvm t, dbc.dbase d
     where f.TableId = t.TVMID
     AND t.databaseID = d.databaseID
     AND f.FieldStatistics IS NOT NULL;

     /* For multiple columns, stats are in Indexes table entry for first
     field: */

     select StatsText (CHAR(255)) FROM
     ((select i.TableID, i.indexnumber, 0 (SMALLINT),
     'COLLECT STATISTICS ON ' || trim(d.Databasename ) || '.' ||
     trim(t.TVMName) || ' COLUMN ('
     from dbc.indexes i, dbc.tvm t, dbc.dbase d
     where i.TableId = t.TVMID
     AND t.databaseID = d.databaseID
     AND i.IndexStatistics IS NOT NULL
     )UNION ALL
     (select i.TableID, i.indexnumber, i.FieldPosition, (CASE WHEN
     i.FieldPosition = 1 THEN ' '  ELSE ',' END) ||  trim(f.Fieldname)
     from dbc.indexes i, dbc.tvfields f
     where i.TableId = f.TableID and i.FieldId = f.FieldID
     AND (i.TableID, i.indexnumber)
     IN (SELECT j.TableID, j.indexnumber FROM dbc.indexes j
     WHERE j.indexstatistics is not null )
     )UNION ALL
     (select i.TableID, i.indexnumber, 32767 (smallint),
     ') ;'
     from dbc.indexes i
     where i.IndexStatistics IS NOT NULL
     )) AS DT(TableID, IndexNumber, FieldPosition, StatsText)
     ORDER BY DT.TableID, DT.indexNumber, DT.FieldPosition;

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