Archives of the TeradataForum
Message Posted: Thu, 18 Apr 2002 @ 18:34:46 GMT
| Subj: || || Re: Examples for a beginner |
| From: || || John K. Wight |
Here is an excellent example that one of my customers created recently and was better that the original one I had. It generates COLLECT
STATISTICS DDL from the DBC DD/DS for all indexed columns in a particular database. Hope this helps further define what Jim Downey
Thanks to the person who wrote this - who (by the way) was new to Teradata, being an ORACLE DBA. The results of this SQL will be SQL
that can then be executed to collect statistics on all indexed columns for a database
(case when indexnumber=1 and columnposition=1
'collect statistics '
(case when columnposition=1
(case when uniqueflag='Y' then 'UNIQUE'
(case when rank() over (partition by databasename, tablename,
indexnumber order by columnposition desc)=1
/*Last column per index */
(case when rank() over (partition by databasename, tablename order by
indexnumber desc, columnposition desc)=1
/*last column definition of all indexes for the table */
then ' on '||trim(databasename)||'.'||trim(tablename)||';'
where databasename= 'xxxx' -- Replace xxx with database name