Query to find tables with no stats defined/old statistics

Database
Enthusiast

Query to find tables with no stats defined/old statistics

Hi,

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.

Thanks

13 REPLIES
Enthusiast

Re: Query to find tables with no stats defined/old statistics

Hi ,

i have a little update on this. i could figure out one quey for this..but i have another question on this..

The single column statistics are stored in dbc.tvfields and the statistics of the indexes (even multicolumn) is stored in dbc.indexes.

If i have stats on multicolumn but they are not indexes, they are not stored in dbc.tvfields and since they are not indexes, they are stored in dbc.indexes. So, where are they stored ?

I wish to find list of tables having no indexes defined

Thx!

Senior Apprentice

Re: Query to find tables with no stats defined/old statistics

You sould check my StatsInfo view:

http://developer.teradata.com/node/9598

Based on this it's like:

SELECT databasename, tablename, MAX(CollectTimestamp)
FROM statsinfo
GROUP BY 1,2
HAVING MIN(MissingStats) = 'Y'
OR MAX(CollectTimestamp) < CURRENT_TIMESTAMP - INTERVAL '30'DAY

Dieter

Enthusiast

Re: Query to find tables with no stats defined/old statistics

Hi Dnoeth,

Your work is great !!

I just tried one code, i saw in some of your post and tweaked a little to macth my requirement. Its gives the same result as you statsinfo view..just wanted to share..comments welcome

SELECT c.databasenamei AS databasename , b.tvmnamei AS tablename

FROM dbc.tvfields a , dbc.tvm b , dbc.dbase c

WHERE a.tableid = b.tvmid

AND b.databaseid = c.databaseid

AND a.fieldstatistics IS null

AND databasename = 'databasename'

-- and b.tablekind='T'

and (databasename,tablename)

not in (

SELECT c.databasenamei AS databasename , b.tvmnamei AS tablename

FROM dbc.tvfields a , dbc.tvm b , dbc.dbase c

WHERE a.tableid = b.tvmid

AND b.databaseid = c.databaseid

AND a.fieldstatistics IS not null

AND databasename = 'databasename'

-- and b.tablekind='T'

union

sel tp.databasename,tp.tablename

from

(

SELECT d.databasenamei AS databasename , t.tvmnamei AS tablename,

MAX(SUBSTR(i.IndexStatistics, 1, 80)) AS STATS

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

/*** Add list of DBs to exclude***/

WHERE d.DatabaseName IN ('databasename')

AND

(i.IndexType IN ('M','S','K','U','H','O','V','P','Q'))

-- and t.tablekind='T'

group by 1,2

HAVING STATS IS not NULL

) tp

)

group by 1,2

Enthusiast

Re: Query to find tables with no stats defined/old statistics

Hi Dnoeth,

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,

Thanks.

Senior Apprentice

Re: Query to find tables with no stats defined/old statistics

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 :-)

Dieter

Enthusiast

Re: Query to find tables with no stats defined/old statistics

Hi Dieter,

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 :-)

-Thanks

Enthusiast

Re: Query to find tables with no stats defined/old statistics

Hi Deiter,

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,

Nilesh

Senior Apprentice

Re: Query to find tables with no stats defined/old statistics

Hi Nilesh,

if you need to get that info you have to talk to your DBA to grant access.

Re: Query to find tables with no stats defined/old statistics

Hi Dieter,

Thanks for putting together the query. When I execute the below query

SELECT databasename, tablename, CollectTimestamp

FROM statsinfo

I am getting null values for collecttimestamp column. I know that we have collected stats on the tables. Any suggestions ?