Home Page for the TeradataForum
 

Archives of the TeradataForum

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


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


Subj:   Re: Statistics expert needed
 
From:   Fred W Pluebell

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>>  
 
 
 
 
 
 
 
 
  
  Top Home Privacy Feedback  
 
 
Copyright for the TeradataForum (TDATA-L), Manta BlueSky    
Copyright 2016 - All Rights Reserved    
Last Modified: 15 Jun 2023