Home Page for the TeradataForum
 
https:

Archives of the TeradataForum

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


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


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 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>>
 
 
 
 
 
 
 
 
 
  
  Top Home Privacy Feedback  
 
 
Copyright for the TeradataForum (TDATA-L), Manta BlueSky    
Copyright 2016 - All Rights Reserved    
Last Modified: 28 Jun 2020