Archives of the TeradataForum

Message Posted: Thu, 29 Jan 2015 @ 09:52:32 GMT

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

Hi,

Has anyone built a process to build TD14 optimised stats collection statements using the DBC metadata?

e.g.
The following can be used to build individual collects stats statements:

     sel 'collect statistics '||
     case when samplesizepct is not null then 'using sample '
     else ''
     end
     ||
     case when indexnumber is not null then 'index '
     else 'column '
     end
     ||'('||trim(columnname)||') on
     '||trim(databasename)||'.'||trim(tablename)||';'
     from dbc.statsv
     where  databasename = 'D_DB1'
     and tablename  = 'T_TBL1'
     ;

     collect statistics using sample column (COLA,COLB,COLC) on D_DB1.T_TBL1; collect
     statistics using sample column (COLC,COLD,COLE) on D_DB1.T_TBL1; collect statistics
     column (COLX,COLY) on D_DB1.T_TBL1; collect statistics column (COLX) on D_DB1.T_TBL1;
     collect statistics column (COLY) on D_DB1.T_TBL1;

But after TD14 the following would be better to optimise the stats collection (Rollup Aggregations - as per Teradata Database 14.0 Statistics Enhancements - orange book, chapter 3):

     COLLECT STATISTICS
                  USING SYSTEM SAMPLE
                 COLUMN (COLA,COLB,COLC) ,
                 COLUMN (COLC,COLD,COLE)
                     ON D_DB1.T_TBL1;
     COLLECT STATISTICS
                 COLUMN (COLX,COLY) ,
                 COLUMN (COLX) ,
                 COLUMN (COLY)
                     ON D_DB1.T_TBL1;

If anything they have to share it would be gratefully received.

Regards,

Martin

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