SHOW Statistics - Meaning of AvgRowsPerBlock

Database
Enthusiast

SHOW Statistics - Meaning of AvgRowsPerBlock

Hi,

Could somebody help me with the AvgRowsPerBlock column returned in SHOW STATISTICS. 

I have a table with  large columns (each about 64KB) and a datablock size of 256KB. On average i would expect 4 rows per data block. BUt if i run SHOW STATISTICS, the returned value is 337.50. Does anybody know why this number is much higher?

Thanks in advance

Roland

COLLECT SUMMARY STATISTICS

                ON TheDatabase.TheTable

            VALUES

(

 /* Version               */ 6,

 /* AvgRowsPerBlock       */ 337.500000,

 /* AvgBlockSize (bytes)  */ 256512.000000,

 /* AvgRowSize            */ 64033.000000,

);

Roland Wenzlofsky
4 REPLIES
Junior Contributor

Re: SHOW Statistics - Meaning of AvgRowsPerBlock

Hi Roland,

I noticed exactly the same:

AvgRowSize and AvgBlockSize are both correct (vertified by Ferret and COLLECT DEMOGRAPHICS) and AvgRowsPerBlock returns values much higher or lower than expected, including impossible values like 80000.

I never care about it :-) 

Teradata Employee

Re: SHOW Statistics - Meaning of AvgRowsPerBlock

Optimizer team has opened a bug report to fix the calculation. Good news is that Dieter's solution to ignore it is fine since that value is not used in query optimization from that source.

Enthusiast

Re: SHOW Statistics - Meaning of AvgRowsPerBlock

I wonder if this information is available somewhere else? I am asking because as far as I know is the usage of secondary indexes dependend from the average rows per data block(in order to determine selectivity)?

I was hoping that this information shown in the SHOW STATISTICS would be used by the Optimizer...and i could use it to estimate index usage.

Roland Wenzlofsky
Teradata Employee

Re: SHOW Statistics - Meaning of AvgRowsPerBlock

You could do the division yourself. It would be close enough. avgblocksize/avgrowsize