Archives of the TeradataForum

Message Posted: Wed, 31 Oct 2007 @ 13:41:53 GMT

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

This query will produce the secondary indexes and most recent used date, and the frequency of use over the specified time period.

You must have DBQL enabled with collect OBJECTS. (Data in the DBC.DBQLOBJTBL).

If you have a PMCP database, or other historical DBQL database use it's DBQLOBJTBL as it is probably better indexed, partitioned, or tuned otherwise for queries.

     SELECT

         IND.DatabaseName,
         IND.TableName,
         IND.Current_Perm,
         IND.Name,
           COALESCE (obj.TheCount,0)  AS FreqUse, obj.mx_date,
         IND.ColumnName,
         IND.IndexNumber,
     CASE
        when IND.IndexType = 'P' then 'NonpartitionedPrimary'
        when IND.IndexType = 'Q' then 'Partitioned Primary'
        when IND.IndexType = 'S' then 'Secondary'
        when IND.IndexType = 'J' then 'join index'
        when IND.IndexType = 'N' then 'hash index'
        when IND.IndexType = 'K' then 'primary key'
        when IND.IndexType = 'U' then 'unique constraint'
        when IND.IndexType = 'V' then 'value ordered secondary'
        when IND.IndexType = 'H' then 'hash ordered ALL covering secondary'
        when IND.IndexType = 'O' then 'valued ordered ALL covering secondary'
        when IND.IndexType = 'I' then 'ordering column of a composite secondary index'
        when IND.IndexType = 'M' then 'Multi-column statistics'
        when IND.IndexType = 'D' then 'Derived column partition statistics'
     ELSE NULL

              END  AS  IndType,
         IND.IndexType TypeAbr


         FROM
          (
           SELECT
             d.databasename AS DatabaseName,
             t.tvmname AS TableName,
            i.IndexNumber as IndexNumber,
            Z.Current_Perm,
             /** Concat up to 16 columnnames into a comma delimited list **/
             MAX(CASE WHEN i.FieldPosition = 1 THEN TRIM(c.FieldName) ELSE ''END) ||
             MAX(CASE WHEN i.FieldPosition = 2 THEN ',' || TRIM(c.FieldName) ELSE '' END) ||
             MAX(CASE WHEN i.FieldPosition = 3 THEN ',' || TRIM(c.FieldName) ELSE '' END) ||
             MAX(CASE WHEN i.FieldPosition = 4 THEN ',' || TRIM(c.FieldName) ELSE '' END) ||
             MAX(CASE WHEN i.FieldPosition = 5 THEN ',' || TRIM(c.FieldName) ELSE '' END) ||
             MAX(CASE WHEN i.FieldPosition = 6 THEN ',' || TRIM(c.FieldName) ELSE '' END) ||
             MAX(CASE WHEN i.FieldPosition = 7 THEN ',' || TRIM(c.FieldName) ELSE '' END) ||
             MAX(CASE WHEN i.FieldPosition = 8 THEN ',' || TRIM(c.FieldName) ELSE '' END) ||
             MAX(CASE WHEN i.FieldPosition = 9 THEN ',' || TRIM(c.FieldName) ELSE '' END) ||
             MAX(CASE WHEN i.FieldPosition = 10 THEN ',' || TRIM(c.FieldName) ELSE '' END) ||
             MAX(CASE WHEN i.FieldPosition = 11 THEN ',' || TRIM(c.FieldName) ELSE '' END) ||
             MAX(CASE WHEN i.FieldPosition = 12 THEN ',' || TRIM(c.FieldName) ELSE '' END) ||
             MAX(CASE WHEN i.FieldPosition = 13 THEN ',' || TRIM(c.FieldName) ELSE '' END) ||
             MAX(CASE WHEN i.FieldPosition = 14 THEN ',' || TRIM(c.FieldName) ELSE '' END) ||
             MAX(CASE WHEN i.FieldPosition = 15 THEN ',' || TRIM(c.FieldName) ELSE '' END) ||
             MAX(CASE WHEN i.FieldPosition = 16 THEN ',' || TRIM(c.FieldName) ELSE '' END) ||
             MAX(CASE WHEN i.FieldPosition > 16  THEN ',...' ELSE '' END) AS ColumnName,
           i.IndexType,
           i.Name
           FROM
             dbc.dbase d
           JOIN dbc.tvm t
             ON d.databaseid = t.databaseid
           JOIN dbc.tvfields c
             ON t.tvmid = c.tableid
           JOIN dbc.Indexes i
             ON c.tableid = i.tableid
             AND c.fieldid = i.fieldid
           JOIN (
                SELECT DATABASENAME, TABLENAME , SUM(CURRENTPERM) AS CURRENT_PERM
                 FROM  dbc.tablesize
     /*            WHERE databasename = */
                 GROUP BY 1,2
                 ) z
             ON d.databasename = z.databasename
                 and t.tvmname = z.tablename

     Where i.IndexType  in ('S','V','H','O')
           /* and d.databasename =*/

           GROUP BY
             d.DatabaseName,
             t.tvmname,
             i.IndexNumber,
                Z.CURRENT_PERM,
             i.IndexType,
             i.Name


          ) IND


       LEFT OUTER JOIN

                  (
                  Select      ObjectDatabasename
                  ,           ObjectTablename
                  ,           ObjectNum
                  ,           sum(FreqofUSe)
                  ,           Count(*)
                 ,            MAX((COLLECTTimestamp (Date))) AS MX_DATE

                  From  dbc.dbqlobjtbl
                  Where ObjectType  =     'I'
                  and   Collecttimestamp(date)  between   '2007-07-01' AND  '2007-10-29'

                  Group By    1,2,3
                  ) obj
                  (Databasename, Tablename, ObjectNum, FreqCount, TheCount, MX_Date)


                  on    ind.Databasename  =     obj.Databasename
                  and   ind.Tablename           =     obj.Tablename
                  and   ind.IndexNumber   =     obj.ObjectNum
     WHERE
                   ind.Databasename = 'edwsc'
                   and current_perm / (2**30)  > 1

     Group By    1,2,3,4,5,6,7,8,9,10
     Order By   IND.Current_Perm DESC,  FreqUse Desc;

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