Archives of the TeradataForum
Message Posted: Thu, 29 Jan 2015 @ 09:52:32 GMT
Has anyone built a process to build TD14 optimised stats collection statements using the DBC metadata?
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.
|Copyright 2016 - All Rights Reserved|
|Last Modified: 27 Dec 2016|