Using Table Stats as a user

Database

Using Table Stats as a user

I know that Teradata uses stats to define the explain plan, and collect lots myself.

However, we are interested in scanning the cardinality of each column in our new results table.

A query like this first one takes 6 minutes to run per column

SELECT 'Col1' AS ColName, COUNT(DISTINCT Col1) AS Counter FROM DBName.TableName;

Whereas this second one only takes 5 seconds, and we are not that bothered about the sampling estimate being slightly out. 

COLLECT STATS USING SAMPLE ON DBName.TableName COLUMN Col1;

Having collected the stats the results are stored in dbc.columnstats.fieldstatistics, but this is a binary packed field.

So  the question is - is there any way for the user to get at these numbers for personal use?

Thanks,

Tony.

2 REPLIES

Re: Using Table Stats as a user

Tony:

HELP STATISTICS gives you the values that you seem to be interested in.

You can query the different tables/views that store the statistics, but you'll need to decipher the 'BYTE' values into numbers.

I think Dieter Noeth has some handy scripts to do that.

I use my own scripts developed from here:

http://carlosal.wordpress.com/2011/12/21/cuando-recopilaste-estadisticas-por-ultima-vez-en-teradata-...

http://carlosal.wordpress.com/2011/12/20/cuando-recopilaste-estadisticas-por-ultima-vez-en-teradata/

HTH

Cheers.

Carlos.

N/A

Re: Using Table Stats as a user

Hi Tony,

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

Dieter