All Forums Database
NewAmigo 27 posts Joined 02/14
19 Feb 2014
Table Stats

Hello,
I am pretty new to teradata. I am creating a simple data cube based on an existing transac table in TD12 and i have been told to collect the stats on a few tables i have created.
So i am using the below query to look for the stats
HELP STATISTICS D_TEST ;, but it is coming up as NO STATS DEFINED FOR THIS TABLE.
Not sure if have to define the statics once i create any table,if so how do i go about doing it, please shed some light.
Thanks

Adeel Chaudhry 773 posts Joined 04/08
19 Feb 2014

You need to select which columns would be used frequently in JOINs and then you need to collect stats using below command:
 
COLLECT STATISTICS ON <Database Name> COLUMN (<Column Name>);
 

-- If you are stuck at something .... consider it an opportunity to think anew.

Adeel Chaudhry 773 posts Joined 04/08
19 Feb 2014

Also .... in case you have some queries handy .... which will be frequently executed .... you can execute following command in a session:
 
DIAGNOSTIC HELPSTATS ON FOR SESSION;
 
And then get the EXPLAIN (F6 key) for getting the explain of the query and it will suggest you some stats to collect. You do need to execute those statements to be able to be in affect.
 
Also note, this command only gives you a hint of stats to be collected. Best case may not be as same as the suggestions.
 
HTH!

-- If you are stuck at something .... consider it an opportunity to think anew.

NewAmigo 27 posts Joined 02/14
20 Feb 2014

Hello Adeel,
Thanks for clarification in the previous posts.
I have used the belwo SQL to get the stats
COLLECT STATISTICS ON TEST COLUMN (TEST1);
and 2 rows have been processed, how do i go about looking at the stats data, where will they be stored?
 

dnoeth 4628 posts Joined 11/04
20 Feb 2014

HELP STATS test COLUMN (test1) shows the actual data.

Dieter

Adeel Chaudhry 773 posts Joined 04/08
20 Feb 2014

You can use HELP STATISTICS <TableName> or from Teradata Administrator you can right-click on a table and click on 'Statistics' to get the stats that are collected.
 
It will list down columns on which stats have been collected and the unique values that are present in that column.
 
Same information is stored in DBC.StatsTbl, but you should prefer using the above 2 methods.
 
HTH!
 

-- If you are stuck at something .... consider it an opportunity to think anew.

NewAmigo 27 posts Joined 02/14
20 Feb 2014

It is working, but only one column at a time. Is there a way we can set stats on a whole table at a single shot. I have tried something like
 
COLLECT STATISTICS ON <DB.Table Name>, but it is not working, i had to set up one column at a time and it is really time consuming.Please suggest!

dnoeth 4628 posts Joined 11/04
20 Feb 2014

You don't want/need to collect stats on each and every column because they must be re-collected from time to time (which is resource-intensive). 
That's why you need to know/decide which columns need stats.
 

Dieter

Adeel Chaudhry 773 posts Joined 04/08
24 Feb 2014

Collecting stats is a step-by-step process, and there is no 100% correct way of doing it. There can be a scenario which works best for you today .... but may not work the same way after some time [due to changed data dynamics].
 
Hence its a continous process and that is why it is on DBAs [usually] to track and add/remove COLLECT STATISTIC commands.
 
Collecting stats on all columns will not ensure your query will now run in a most optimized manner .... rather only collecting correct stats can ensure that.

-- If you are stuck at something .... consider it an opportunity to think anew.

shivkumar 6 posts Joined 07/13
20 Sep 2015

Hi Dieter,
 
HELP STATS test COLUMN (test1)
 
Above query is not working in TD15. Please help on this.
 
Thanks,
Shivkumar

dnoeth 4628 posts Joined 11/04
20 Sep 2015

Hi Shivkumar,
of course it's not working anymore, since TD14 there's a new syntax :-)
SHOW STATS VALUES COLUMN (test1) ON test;

Dieter

You must sign in to leave a comment.