Archives of the TeradataForum

Message Posted: Tue, 26 Sep 2006 @ 12:50:02 GMT

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

I use the following SQLs to generate SQL to do Collect Stat. I will export these results into a file and and do a ".run file <filename>" in BTEQ. [I will get the statistics collected as it were in the source table.]

     SELECT   DISTINCT '' (title '--'),
     CASE    WHEN  CSUM(1,IND.IndexNumber)= 1 OR IND.IndexName IS NOT NULL THEN 'COLLECT STAT ON ${DBName}.${TableName}' ELSE '' END Action
     ,CASE
             WHEN CSUM(1,IND.IndexNumber) = 1 AND IND.IndexName IS NULL THEN 'INDEX ('
             WHEN CSUM(1,IND.IndexNumber) <> 1 AND IND.IndexName IS NULL  THEN ', ' ELSE 'INDEX '
     END
     || TRIM(COALESCE(IndexName,COLUMNNAME))
     ||CASE WHEN DTL0.MAXSEQ = CSUM(1,IND.IndexNumber) AND IND.IndexName IS NULL THEN ')'  ELSE '' END
     ,CASE  WHEN DTL0.MAXSEQ = CSUM(1,IND.IndexNumber) OR IND.IndexName IS NOT NULL
     THEN ';'  ELSE '' END
     FROM DBC.INDICES IND LEFT OUTER JOIN
     (SEL
                     DatabaseName
                     ,TableName
                     ,IndexNumber
                     ,COUNT( IndexNumber) AS MAXSEQ
             FROM
                     DBC.INDICES
             WHERE
                     DatabaseName = '${DBName}'
             AND     TableName = '${TableName}'
             AND     IndexName IS NULL
             GROUP BY 1,2,3
             )DTL0
     ON      DTL0.IndexNumber= IND.IndexNumber
     WHERE
             IND.DatabaseName = '${DBName}'
     AND     IND.TableName = '${TableName}'
     GROUP BY IND.IndexNumber
     ORDER BY IND.IndexNumber,Action DESC;
     .if errorcode <> 0 then .quit 5;

     sel CollStatQry(title '') from (
     SEL 'COLLECT STAT ON '||TRIM(DatabaseName)||'.'||TRIM(TableName)||' COLUMN('||TRIM(COLUMNNAME)||');' AS CollStatQry
     ,0(integer) as StatisticsId
     ,0(integer) as ColumnPosition
     FROM DBC.COLUMNSTATS
     WHERE
             DatabaseName='${DBName}'
     AND     TableName = '${TableName}'
     AND     FieldStatistics IS NOT NULL

     UNION

     SEL
     case ColumnPosition
             when  1 then 'COLLECT STAT ON '||TRIM(DatabaseName)||'.'||TRIM(TableName)||' '||'COLUMN('||trim(ColumnName)
             when  IndxColCnt then ','||trim(ColumnName)||');'
             else ','||trim(ColumnName)
             end as CollStatQry
             ,MCS.StatisticsId
             ,MCS.ColumnPosition
     FROM
             DBC.MULTICOLUMNSTATS MCS,
             (sel    COUNT(*) as IndxColCnt
                     ,StatisticsId
             from
                     DBC.MULTICOLUMNSTATS
             WHERE   DatabaseName='${DBName}'
             AND     TableName = '${TableName}'
             group by 2)DTL0
     WHERE
             DatabaseName='${DBName}'
     AND     TableName = '${TableName}'
     and     MCS.StatisticsId=DTL0.StatisticsId
             )DTL
      Order By StatisticsId ,ColumnPosition;

thanks,

Jinesh P V

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