 |
 |
Archives of the TeradataForum
Message Posted: Wed, 05 Jan 2011 @ 19:50:47 GMT
| Subj: | | Re: Copying Statistics from one table to another |
| |
| From: | | Barner, Eric |
For non Teradata 13 or Non-identical tables, here is a piece of code (macro) you can use to generate the statistics statements from a source
table.
You can modify this easily to add extra parameters to generate code to take into account for a target table if desired.
Eric W. Barner
USAGE:
EXEC SYS_MGMT.stats_create ('databasename','tablename');
REPLACE macro SYS_MGMT.stats_create (dbname varchar(30) , tblname
varchar(30)) as ( select col1|| ' '||col2|| ' '||col3 (varchar (10000)) FROM
( SELECT 'COLLECT STATS "'||TRIM(databasename)||'".'||TRIM(tablename)||'
INDEX '
AS col1
, (indexname)AS col2
, ' ;' as col3
FROM
(
SEL databasename
,tablename
,TRIM(INDEXNAME) AS INDEXNAME
,count(*) as n_cols
FROM dbc.INDEXSTATS
WHERE databasename = :dbname
and tablename = :tblname
and indexstatistics is not null
and indexname is not null
group by 1,2,3
) dt
) A
UNION
select col1|| ' '||col2|| ' '||col3 (varchar (10000)) FROM ( SELECT
'COLLECT STATS "'||TRIM(databasename)||'".'||TRIM(tablename)||' INDEX ('
AS col1
, MAX(CASE WHEN columnposition =1 THEN ColumnName ELSE '' END) || MAX(CASE
WHEN columnposition =2 THEN ',' || ColumnName ELSE '' END) || MAX(CASE WHEN
columnposition =3 THEN ',' || ColumnName ELSE '' END) || MAX(CASE WHEN
columnposition =4 THEN ',' || ColumnName ELSE '' END) || MAX(CASE WHEN
columnposition =5 THEN ',' || ColumnName ELSE '' END) || MAX(CASE WHEN
columnposition =6 THEN ',' || ColumnName ELSE '' END) || MAX(CASE WHEN
columnposition =7 THEN ',' || ColumnName ELSE '' END) || MAX(CASE WHEN
columnposition =8 THEN ',' || ColumnName ELSE '' END) || MAX(CASE WHEN
columnposition =9 THEN ',' || ColumnName ELSE '' END) || MAX(CASE WHEN
columnposition =10 THEN ',' || ColumnName ELSE '' END) || MAX(CASE WHEN
columnposition =11 THEN ',' || ColumnName ELSE '' END) || MAX(CASE WHEN
columnposition =12 THEN ',' || ColumnName ELSE '' END) || MAX(CASE WHEN
columnposition =13 THEN ',' || ColumnName ELSE '' END) || MAX(CASE WHEN
columnposition =14 THEN ',' || ColumnName ELSE '' END) || MAX(CASE WHEN
columnposition =15 THEN ',' || ColumnName ELSE '' END) || MAX(CASE WHEN
columnposition =16 THEN ',' || ColumnName ELSE '' END) || MAX(CASE WHEN
columnposition =17 THEN ',' || ColumnName ELSE '' END) || MAX(CASE WHEN
columnposition =18 THEN ',' || ColumnName ELSE '' END) || MAX(CASE WHEN
columnposition =19 THEN ',' || ColumnName ELSE '' END) || MAX(CASE WHEN
columnposition =20 THEN ',' || ColumnName ELSE '' END) || MAX(CASE WHEN
columnposition =21 THEN ',' || ColumnName ELSE '' END) || MAX(CASE WHEN
columnposition =22 THEN ',' || ColumnName ELSE '' END) || MAX(CASE WHEN
columnposition =23 THEN ',' || ColumnName ELSE '' END) || MAX(CASE WHEN
columnposition =24 THEN ',' || ColumnName ELSE '' END) || MAX(CASE WHEN
columnposition =25 THEN ',' || ColumnName ELSE '' END) || MAX(CASE WHEN
columnposition =26 THEN ',' || ColumnName ELSE '' END) || MAX(CASE WHEN
columnposition =27 THEN ',' || ColumnName ELSE '' END) || MAX(CASE WHEN
columnposition =28 THEN ',' || ColumnName ELSE '' END) || MAX(CASE WHEN
columnposition =29 THEN ',' || ColumnName ELSE '' END) || MAX(CASE WHEN
columnposition =30 THEN ',' || ColumnName ELSE '' END) || MAX(CASE WHEN
columnposition =31 THEN ',' || ColumnName ELSE '' END) || MAX(CASE WHEN
columnposition =32 THEN ',' || ColumnName ELSE '' END) || MAX(CASE WHEN
columnposition =33 THEN ',' || ColumnName ELSE '' END) || MAX(CASE WHEN
columnposition =34 THEN ',' || ColumnName ELSE '' END) || MAX(CASE WHEN
columnposition =35 THEN ',' || ColumnName ELSE '' END) || MAX(CASE WHEN
columnposition =36 THEN ',' || ColumnName ELSE '' END) || MAX(CASE WHEN
columnposition =37 THEN ',' || ColumnName ELSE '' END) || MAX(CASE WHEN
columnposition =38 THEN ',' || ColumnName ELSE '' END) || MAX(CASE WHEN
columnposition =39 THEN ',' || ColumnName ELSE '' END) || MAX(CASE WHEN
columnposition =40 THEN ',' || ColumnName ELSE '' END) || MAX(CASE WHEN
columnposition =41 THEN ',' || ColumnName ELSE '' END) || MAX(CASE WHEN
columnposition =42 THEN ',' || ColumnName ELSE '' END) || MAX(CASE WHEN
columnposition =43 THEN ',' || ColumnName ELSE '' END) || MAX(CASE WHEN
columnposition =44 THEN ',' || ColumnName ELSE '' END) || MAX(CASE WHEN
columnposition =45 THEN ',' || ColumnName ELSE '' END) || MAX(CASE WHEN
columnposition =46 THEN ',' || ColumnName ELSE '' END) || MAX(CASE WHEN
columnposition =47 THEN ',' || ColumnName ELSE '' END) || MAX(CASE WHEN
columnposition =48 THEN ',' || ColumnName ELSE '' END) || MAX(CASE WHEN
columnposition =49 THEN ',' || ColumnName ELSE '' END) || MAX(CASE WHEN
columnposition =50 THEN ',' || ColumnName ELSE '' END) || MAX(CASE WHEN
columnposition =51 THEN ',' || ColumnName ELSE '' END) || MAX(CASE WHEN
columnposition =52 THEN ',' || ColumnName ELSE '' END) || MAX(CASE WHEN
columnposition =53 THEN ',' || ColumnName ELSE '' END) || MAX(CASE WHEN
columnposition =54 THEN ',' || ColumnName ELSE '' END) || MAX(CASE WHEN
columnposition =55 THEN ',' || ColumnName ELSE '' END) || MAX(CASE WHEN
columnposition =56 THEN ',' || ColumnName ELSE '' END) || MAX(CASE WHEN
columnposition =57 THEN ',' || ColumnName ELSE '' END) || MAX(CASE WHEN
columnposition =58 THEN ',' || ColumnName ELSE '' END) || MAX(CASE WHEN
columnposition =59 THEN ',' || ColumnName ELSE '' END) || MAX(CASE WHEN
columnposition =60 THEN ',' || ColumnName ELSE '' END) || MAX(CASE WHEN
columnposition =61 THEN ',' || ColumnName ELSE '' END) || MAX(CASE WHEN
columnposition =62 THEN ',' || ColumnName ELSE '' END) || MAX(CASE WHEN
columnposition =63 THEN ',' || ColumnName ELSE '' END) || MAX(CASE WHEN
columnposition =64 THEN ',' || ColumnName ELSE '' END) AS col2 , ' );' as
col3 FROM ( SEL A.databasename ,A.tablename ,A.columnposition
,TRIM(A.columnName) AS ColumnName
,count(*) as n_cols
FROM dbc.INDEXSTATS a
join
(
SELECT DISTINCT DATABASENAME, TABLENAME FROM dbc.INDEXSTATS WHERE
indexstatistics is not null and indexname is null
) B
on A.DATABASENAME = B.DATABASENAME
AND A.TABLENAME= B.TABLENAME
WHERE A.databasename = :DBNAME
AND B.TABLENAME = :TBLNAME
and A.indexname is null
group by 1,2,3,4
) dt
GROUP BY 1
) A
UNION
select col1|| ' '||col2|| ' '||col3 (varchar (10000))
FROM
(
SELECT STATISTICSID,
'COLLECT STATS "'||TRIM(databasename)||'".'||TRIM(tablename)||' COLUMN
(' AS col1
, MAX(CASE WHEN columnposition =1 THEN ColumnName ELSE '' END) ||
MAX(CASE WHEN columnposition =2 THEN ',' || ColumnName ELSE '' END) ||
MAX(CASE WHEN columnposition =3 THEN ',' || ColumnName ELSE '' END) ||
MAX(CASE WHEN columnposition =4 THEN ',' || ColumnName ELSE '' END) ||
MAX(CASE WHEN columnposition =5 THEN ',' || ColumnName ELSE '' END) ||
MAX(CASE WHEN columnposition =6 THEN ',' || ColumnName ELSE '' END) ||
MAX(CASE WHEN columnposition =7 THEN ',' || ColumnName ELSE '' END) ||
MAX(CASE WHEN columnposition =8 THEN ',' || ColumnName ELSE '' END) ||
MAX(CASE WHEN columnposition =9 THEN ',' || ColumnName ELSE '' END) ||
MAX(CASE WHEN columnposition =10 THEN ',' || ColumnName ELSE '' END) ||
MAX(CASE WHEN columnposition =11 THEN ',' || ColumnName ELSE '' END) ||
MAX(CASE WHEN columnposition =12 THEN ',' || ColumnName ELSE '' END) ||
MAX(CASE WHEN columnposition =13 THEN ',' || ColumnName ELSE '' END) ||
MAX(CASE WHEN columnposition =14 THEN ',' || ColumnName ELSE '' END) ||
MAX(CASE WHEN columnposition =15 THEN ',' || ColumnName ELSE '' END) ||
MAX(CASE WHEN columnposition =16 THEN ',' || ColumnName ELSE '' END) ||
MAX(CASE WHEN columnposition =17 THEN ',' || ColumnName ELSE '' END) ||
MAX(CASE WHEN columnposition =18 THEN ',' || ColumnName ELSE '' END) ||
MAX(CASE WHEN columnposition =19 THEN ',' || ColumnName ELSE '' END) ||
MAX(CASE WHEN columnposition =20 THEN ',' || ColumnName ELSE '' END) ||
MAX(CASE WHEN columnposition =21 THEN ',' || ColumnName ELSE '' END) ||
MAX(CASE WHEN columnposition =22 THEN ',' || ColumnName ELSE '' END) ||
MAX(CASE WHEN columnposition =23 THEN ',' || ColumnName ELSE '' END) ||
MAX(CASE WHEN columnposition =24 THEN ',' || ColumnName ELSE '' END) ||
MAX(CASE WHEN columnposition =25 THEN ',' || ColumnName ELSE '' END) ||
MAX(CASE WHEN columnposition =26 THEN ',' || ColumnName ELSE '' END) ||
MAX(CASE WHEN columnposition =27 THEN ',' || ColumnName ELSE '' END) ||
MAX(CASE WHEN columnposition =28 THEN ',' || ColumnName ELSE '' END) ||
MAX(CASE WHEN columnposition =29 THEN ',' || ColumnName ELSE '' END) ||
MAX(CASE WHEN columnposition =30 THEN ',' || ColumnName ELSE '' END) ||
MAX(CASE WHEN columnposition =31 THEN ',' || ColumnName ELSE '' END) ||
MAX(CASE WHEN columnposition =32 THEN ',' || ColumnName ELSE '' END) ||
MAX(CASE WHEN columnposition =33 THEN ',' || ColumnName ELSE '' END) ||
MAX(CASE WHEN columnposition =34 THEN ',' || ColumnName ELSE '' END) ||
MAX(CASE WHEN columnposition =35 THEN ',' || ColumnName ELSE '' END) ||
MAX(CASE WHEN columnposition =36 THEN ',' || ColumnName ELSE '' END) ||
MAX(CASE WHEN columnposition =37 THEN ',' || ColumnName ELSE '' END) ||
MAX(CASE WHEN columnposition =38 THEN ',' || ColumnName ELSE '' END) ||
MAX(CASE WHEN columnposition =39 THEN ',' || ColumnName ELSE '' END) ||
MAX(CASE WHEN columnposition =40 THEN ',' || ColumnName ELSE '' END) ||
MAX(CASE WHEN columnposition =41 THEN ',' || ColumnName ELSE '' END) ||
MAX(CASE WHEN columnposition =42 THEN ',' || ColumnName ELSE '' END) ||
MAX(CASE WHEN columnposition =43 THEN ',' || ColumnName ELSE '' END) ||
MAX(CASE WHEN columnposition =44 THEN ',' || ColumnName ELSE '' END) ||
MAX(CASE WHEN columnposition =45 THEN ',' || ColumnName ELSE '' END) ||
MAX(CASE WHEN columnposition =46 THEN ',' || ColumnName ELSE '' END) ||
MAX(CASE WHEN columnposition =47 THEN ',' || ColumnName ELSE '' END) ||
MAX(CASE WHEN columnposition =48 THEN ',' || ColumnName ELSE '' END) ||
MAX(CASE WHEN columnposition =49 THEN ',' || ColumnName ELSE '' END) ||
MAX(CASE WHEN columnposition =50 THEN ',' || ColumnName ELSE '' END) ||
MAX(CASE WHEN columnposition =51 THEN ',' || ColumnName ELSE '' END) ||
MAX(CASE WHEN columnposition =52 THEN ',' || ColumnName ELSE '' END) ||
MAX(CASE WHEN columnposition =53 THEN ',' || ColumnName ELSE '' END) ||
MAX(CASE WHEN columnposition =54 THEN ',' || ColumnName ELSE '' END) ||
MAX(CASE WHEN columnposition =55 THEN ',' || ColumnName ELSE '' END) ||
MAX(CASE WHEN columnposition =56 THEN ',' || ColumnName ELSE '' END) ||
MAX(CASE WHEN columnposition =57 THEN ',' || ColumnName ELSE '' END) ||
MAX(CASE WHEN columnposition =58 THEN ',' || ColumnName ELSE '' END) ||
MAX(CASE WHEN columnposition =59 THEN ',' || ColumnName ELSE '' END) ||
MAX(CASE WHEN columnposition =60 THEN ',' || ColumnName ELSE '' END) ||
MAX(CASE WHEN columnposition =61 THEN ',' || ColumnName ELSE '' END) ||
MAX(CASE WHEN columnposition =62 THEN ',' || ColumnName ELSE '' END) ||
MAX(CASE WHEN columnposition =63 THEN ',' || ColumnName ELSE '' END) ||
MAX(CASE WHEN columnposition =64 THEN ',' || ColumnName ELSE '' END)
AS col2 ,
' );' as col3
FROM
(
SEL
databasename
,tablename
,columnposition
,STATISTICSID
,TRIM(columnName) AS ColumnName
FROM
dbc.MultiColumnStats
WHERE databasename = :DBNAME
and TABLENAME = :TBLNAME
) dt
GROUP BY 1 ,2
) A
UNION
select col1|| ' '||col2|| ' '||col3 (varchar (10000))
FROM
(
SEL
'COLLECT STATS "'||TRIM(databasename)||'".'||TRIM(tablename)||' COLUMN
(' AS col1,
(Columnname) as col2,
');' as col3
FROM
(
SEL
databasename
,tablename
,TRIM(columnName) AS ColumnName
FROM
dbc.Columnstats
WHERE databasename= :DBNAME
AND TABLENAME=:TBLNAME
and FieldStatistics is not null
) dt
) a
;
);
| |
