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