Parsing Help Statistics Output

Database
Enthusiast

Parsing Help Statistics Output

What is the best way to extract the output of the 'Help statistics DbName.TblName column Partition;", for the purpose of extracting the value of the column 'Number of Rows'. If you have attempted this through SQL, please share your thoughts. What I've already have in place is the following methodology...

Run 'Collect Statistics on DbName.TblName Column (Partition);

Run 'Help Statistics DbName.TblName Column (Partition);' through BTEQ with SideTitles on

Extract the first Numbe of Rows and capture the value for the field.

While thats one way to do it, looking for the most optimal way to do this.(Currently on 12.x, not on 13.x yet, where the Sel count(*) would provide the same speed).

7 REPLIES
Junior Contributor

Re: Parsing Help Statistics Output

Go to http://www.teradataforum.com/attach.htm and download my stats query to extract that info for all existing stats using a single request   :-)

Dieter

Enthusiast

Re: Parsing Help Statistics Output

Thanks for the clear documentation on this. I'm using this information/SQL to find out what is stored in the dictionary. But, when I want get the current row count, I'm not doing the traditional row count ,which is expensive, instead I do a coll stats on tb column partition and then help stats on col partition. while this is faster, the information is not updated in the 3 tables,but help stats is able to get this which means that help stats gets this info from another undisclosed location(atleast unknown to me) and thats where I'm seeking help.
Junior Contributor

Re: Parsing Help Statistics Output

Partition stare stored in dbc.indexes with an IndexType of 'D'.

My query should return that info, too. You should check if you're using the latest version from the link above.

Currently i'm enhancing it (including using SQL UDFs in TD13.10) and i'll post a blog or article on it when i'm finished :-)

Dieter

Enthusiast

Re: Parsing Help Statistics Output

This is awesome, thanks for the code. Now I can do all the instrumentation from within the database !!

Fan

Re: Parsing Help Statistics Output

Dieter, this is great!!!

I have a small variation, and not sure if your query can be modfied to get the info.

I am looking for Rowcount in each partition for a table. Rather the total row count of a table, is it possible to get the row count for each partiiton for an object.

-Biren

Junior Contributor

Re: Parsing Help Statistics Output

Hi Biren,

of course the technique to extract those float values could be extended to retrieve row counts for partitions (i did something similar for dates once).

But his would only be possible if the number of partitions is less than approx. 400, as the number of intervals is limited.

Dieter

Fan

Re: Parsing Help Statistics Output

Thank you Dieter. 400 is good enough, I am looking for some pointers not the exact replica of actual table. So let me play around and see what I find.

-Biren