Archives of the TeradataForum

Message Posted: Wed, 13 Feb 2008 @ 13:22:16 GMT

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

Good afternoon Gentlemen,

I'm trying to use such a query, which has been found here.

This one can't show FreqUse column properly, it inserts only 0 to all the FreqUse fields.

Is this bug or feature? :-)

Cheers,

Roman

     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','P','Q')
     /* 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'
     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 in
     (
     SELECT databasename FROM dbc.databases
     )
     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>>