I need a query to find the the tables in a database for which there are no stats defined yet (This will help me to tell uses to define stats). Also, i want to list down the tables in a database for which the stats are old (say 1 month old).
I dont want to do a help stats on every tables and find the details (practically not possible). Is there a query for this.
This can be done using the dbc.tvfields and dbc.index (my guess), but i m not sure how.
If someone has the query , then it will be very helpful.
You sould check my StatsInfo view:
Based on this it's like:
SELECT databasename, tablename, MAX(CollectTimestamp)
GROUP BY 1,2
HAVING MIN(MissingStats) = 'Y'
OR MAX(CollectTimestamp) < CURRENT_TIMESTAMP - INTERVAL '30'DAY
I was looking for your statsinfo views and new version of your stats query which has been fixed all the bugs so for.
I tried with no luck on all of your blogs here, even in http://developer.teradata.com/node/9598
I looked at John Hall's TeradataForum, but that is old version.
Please help me,
I just noticed that the attachement is lost, i'll send it to you directly.
There was some maintenance going on this week and apparently it's still not back to normal, you were the guy with the empty comment on my blog :-)
Yesterday, I got those attachments in http://developer.teradata.com/node/9598, thanks for that, also it looks like sites are back to normal.
The other day, I was trying to put comment on your blog, but it was not allowing me. But I accidentally pressed submit button and it registered an empty comment :-)
How are you?
I was going through above blog. Actually i dont have access to dbc.tvfields.
Is there a way i can get the missing collect stats table and their index in the same query.
Thanks for putting together the query. When I execute the below query
I am getting null values for collecttimestamp column. I know that we have collected stats on the tables. Any suggestions ?