Re: average row length for a table

On Apr 4, 7:32 am, "MTNorman" <mtnor..._at_duke-energy.com> wrote:
> On Apr 3, 2:31 pm, "Michel Cadot" <micadot{at}altern{dot}org> wrote:
>
>
>
>
>
> > "MTNorman" <mtnor..._at_duke-energy.com> a écrit dans le message de news: 1175623636.920607.146..._at_w1g2000hsg.googlegroups.com...
> > | What's the difference between the vsize query below (from MetaLink
> > | Note 10640.1) and the dba_tables.avg_row_len value calculated using
> > | dbms_stats compute?
> > |
> > | SELECT AVG(NVL(VSIZE(A), 1)) +
> > | AVG(NVL(VSIZE(B), 1)) +
> > | AVG(NVL(VSIZE(C), 1)) "SPACE OF AVERAGE ROW"
> > | FROM test;
> > |
> > | On one 46 million row table, the vsize query returns 57 and dbms_stats
> > | computes 62 as the avg_row_len. Version 9.2.0.6 on AIX 5.3 in an
> > | uniform extent LMT with manual segment space management.
> > |
>
> > column and row headers.
>
> > Regards
> > Michel Cadot
>
> The row header is 13 bytes for this table using the Note 10640.1
> formula - so the 5 bytes difference should not be the row header.- Hide quoted text -
>
> - Show quoted text -

The row header is 3 bytes, not 13. From the referenced note:
>> row header = 3 bytes per row of a non-clustered table <<

Also I believe you will find that at least some of the formulas in the note are only approximations rather than exact calculations so a difference in your calculations compared to the value generated by analyze or dbms_stats is to be expected. Also in the case of dbms_stats I have seen a note claiming that the avg_row_len generated by dbms_stats does not include the 3 byte row header in the avg_row_len as analyze does. This would be a version specific issue.

HTH -- Mark D Powell -- Received on Wed Apr 04 2007 - 09:12:02 CDT