Archives of the TeradataForum

Message Posted: Tue, 02 Dec 2003 @ 17:17:43 GMT

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

Hi Prabhjot,

I think the following example may do what you're after. I can't see a way around not having a control table to indicate which columns need to be grouped together for a stats collection so the first table is used to indicate column name, which group of columns to collect stats on it belongs to and the position within the group.

Regards,

Tris Bayliff.

--------------------------------------------

-- Create temp table to store columns and hierarchy
 create volatile table stats_control
    (columnname        char(30)
    ,statsgroup        byteint
    ,colpos            byteint
    )
 index (columnname, statsgroup, colpos)
 on commit preserve rows
 ;

-- insert columnname, grouping sequence number, col sequence number
within group
   insert into stats_control ('col2',1, 1)
 ; insert into stats_control ('col3',2, 1)
 ; insert into stats_control ('col4',2, 2)
 ; insert into stats_control ('col4',3, 1)
 ;

-- Build working table with details of all databases and tables
 create volatile table stats_control_tmp
 as (select    b.databasename
              ,b.tablename
              ,a.columnname
              ,a.statsgroup
              ,a.colpos
     from      stats_control a
              ,dbc.columns   b
     where     a.columnname = b.columnname
     group by  1, 2, 3, 4, 5
    )
 with data
 unique primary index (databasename, tablename, columnname, statsgroup, colpos)
 on commit preserve rows
 ;

-- Generate collect stats SQL
 select    case
             when a.colpos = 1
                then   ' collect stats on '
                     ||trim(a.databasename)
                     ||'.'
                     ||trim(a.tablename)
             else ''
           end (title '')
          ,case
             when a.colpos = 1
                then   'column ('
                     ||trim(a.columnname)
             else '       ,'||trim(a.columnname)
           end (title '')
          ,case
             when a.colpos      = dt.max_pos
                then ');'
             else ' '
           end (title '')
 from      stats_control_tmp a
          ,(select    databasename as max_db
                     ,tablename    as max_tb
                     ,statsgroup   as max_grp
                     ,max(colpos)  as max_pos
            from      stats_control_tmp
            group by  1, 2, 3
           ) dt
 where     a.databasename = dt.max_db
 and       a.tablename    = dt.max_tb
 and       a.statsgroup   = dt.max_grp
 order by  a.databasename
          ,a.statsgroup
          ,a.tablename
          ,a.colpos
 ;

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