新的Teradata统计功能。困惑了几件事

问题描述：

在这里，我处于同一个泥潭，也许你们中的很多人都是。 Teradata 14.1的对象使用功能已启用。
此专栏是1 env。结果是零脯氨酸，因为我们升级了一段时间，但在另一个（QA /测试）env。它有价值。

DBC.DBQLRuleTbl | TimeCreated

sel TimeCreated from DBC.DBQLRuleTbl where DBQLRuleTbl.UserID in (sel OU.DatabaseId from DBC.ObjectUsage OU)

在1个ENV没有行。我想要做的是找出未使用的统计数据。我们只在过去的几个月才得到OU。所以，当我使用这个查询来找出UNUSED统计信息时，我得到最后一个col -1，因为RuleTbl没有任何与OU表相匹配的东西。新的Teradata统计功能。困惑了几件事

 SELECT DBC.DBase.DatabaseName AS DatabaseName 
      ,DBC.TVM.TVMName  AS TableName 
      ,COALESCE(DBC.StatsTbl.StatsName 
        ,DBC.StatsTbl.ExpressionList 
        ,'SUMMARY') AS StatName 
      ,CURRENT_DATE - CAST(DBC.StatsTbl.CreateTimeStamp AS DATE) AS StatAge 
      ,CASE 
      WHEN DatabaseName = 'DBC' 
      THEN -2 -- Logging Not Applicable 
      WHEN DBC.StatsTbl.StatsType IN ('B', 'M') 
      THEN -2 -- Logging Not Applicable on Temp tables (base and materialized) 
      WHEN DBC.DBQLRuleTbl.TimeCreated IS NULL 
      THEN -1 -- Logging Not Enabled 
      WHEN DBC.DBQLRuleTbl.TimeCreated > DBC.StatsTbl.CreateTimeStamp 
      THEN CURRENT_DATE - CAST(DBC.DBQLRuleTbl.TimeCreated AS DATE) 
      ELSE CURRENT_DATE - CAST(DBC.StatsTbl.CreateTimeStamp AS DATE) 
    END AS DaysStatLogged 
    FROM DBC.StatsTbl LEFT JOIN DBC.DBQLRuleTbl 
       ON DBC.StatsTbl.DatabaseId = DBC.DBQLRuleTbl.UserID 
       AND DBQLRuleTbl.ExtraField5 = 'T' 

      ,DBC.Dbase 
      ,DBC.TVM  
    WHERE DBC.StatsTbl.DatabaseId = DBC.DBASE.DatabaseId 
     AND DBC.StatsTbl.ObjectId = DBC.TVM.TVMId 
     AND NOT EXISTS (SELECT '1' FROM DBC.ObjectUsage OU 
         WHERE OU.UsageType = 'STA' 
         AND OU.DatabaseId = DBC.StatsTbl.DatabaseId 
         AND OU.ObjectId = DBC.StatsTbl.ObjectId 
         AND OU.FieldId = DBC.StatsTbl.StatsId 
         AND CURRENT_DATE - CAST(OU.LastAccessTimeStamp AS DATE) < N 
        ) 
     --AND DaysStatLogged > N 

    /* I had to mute this criteria because otherwise there will be no o/p for reasons mentioned above */ 

     AND DBC.StatsTbl.StatsId <> 0 
-- Do not qualify table-level SUMMARY statistics as unused 
-- May get implicitly used but not recorded as used 
and StatAge < N 

/* StatAge < N .I am not sure if this filter should be there- I added it to catch those stats that are as old OU. We dunno if stats older than OU ever got used before OU started */ 
            and databasename in (sel child from dbc.children where parent ='FINDB' group by  1) 
    ORDER BY 1, 2, 3;

我想获取FINDB级联而不是系统范围统计信息。鉴于我的规则表搞砸了 - 不上面的查询最接近适应该法案
“查找我的统计信息FINDB下的所有数据库没有被使用，因为OU被打开”

答

该查询可能是好的，因为它是基于Identifying Used, Unused and Missing Statistics，只需检查它是否返回您想要的。

但是你在TD14.10和统计经理大腹便便的观点做一切（及以上），如果你不使用它，但是，你应该开始Easing Into Using the New AutoStats Feature

新的Teradata统计功能。困惑了几件事

相关推荐