Archives of the TeradataForum

Message Posted: Thu, 18 Apr 2002 @ 18:34:46 GMT

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

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 referenced below.

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

 select
       (case when indexnumber=1 and columnposition=1
                then
                        'collect statistics '
                else ''
                end)
       ||
       (case when columnposition=1
            then
             (case when uniqueflag='Y' then 'UNIQUE'
                     else ''
              end)
            ||' INDEX('||TRIM(columnname)
       else
             ','||TRIM(columnname)
       end)
       ||
       (case when rank() over (partition by databasename, tablename,
indexnumber order by                            columnposition desc)=1
                /*Last column per index */
            then ')'
            else ''
       end)
       ||
       (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)||';'
            else ''
       end)
    from
                dbc.indexstats b
   where     databasename= 'xxxx'   -- Replace xxx with database name
    order by
                databasename
                , tablename
                , indexnumber
                , columnposition;

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