Stats Collection issue with UniqueValueCount in dbc.statsv table and actual unique count

Database
Teradata Employee

Stats Collection issue with UniqueValueCount in dbc.statsv table and actual unique count

When we collect Stats on a particular column (index) the count shown under 

 

SEL  UniquevalueCount FROM dbc.statsv WHERE databasename='dbname' AND TABLENAME = 'tablename' AND columnname='mycolumn'

This count let say is 2 million .

But actual table has count of 50 million.

SEL COUNT(DISTINCT mycolumn) FROM DBNAME.TABLENAME

Does anyone know the reason for this.

Because of this Teradata is redistributing the table across all AMPS when joining with a table having count of 50 mil and causing Spool issues for me.

Dropping and recreating solves the problem and gives the right count , but this shouldn't be happening.

 

Tags (3)
3 REPLIES
Junior Contributor

Re: Stats Collection issue with UniqueValueCount in dbc.statsv table and actual unique count

 

Dropping and recreating solves the problem and gives the right count , but this shouldn't be happening. 


Dropping & recreating the stats?

 

Can post more details about following dbc.StatsV column:

SampleSignature

SampleSizePct

RowCount

NullCount

StatsSkipCount

 

Can you compare the numbers for HELP STATS vs. HELP CURRENT STATS?

Teradata Employee

Re: Stats Collection issue with UniqueValueCount in dbc.statsv table and actual unique count

I don't have Much details left since we when we dropped the stats and re collected the stats on that particular column It got resolved to the right value.

But I do have these values from my answer set.

SampleSignature :  Global Default

SampleSizePct : 5.00

RowCount : 162 million 

NullCount : 0

StatsSkipCount : 0 

 

 

Junior Contributor

Re: Stats Collection issue with UniqueValueCount in dbc.statsv table and actual unique count

This is the result from the new stats?

The old one was probably a sampled statistic, too, this might result in a wrong estimation.

Is that table partitioned and the column is related to the partitioning column?

What's the data type of that column?

 

You can check the history records returned by SHOW STATS VALUES to see how the sample size changes.

Of course you might consider to force a larger sample using the USING SAMPLE n PERCENT syntax.