On Apr 3, 4:13 pm, Brian Peasland <d..._at_nospam.peasland.net> wrote:
> MTNorman wrote:
> > 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.
>
> In your query above, you are summing the average of each column which
> AVG_ROW_LEN is the average for the entire row. The average of the pieces
> does not always equal the average of the whole. Hence, you can have two
> different results. Maybe your query should be written as:
>
> SELECT AVG(VSIZE(A)+VSIZE(B)+VSIZE(C)) FROM test;
>
> Cheers,
> Brian
>
This sounded very reasonable to me; unfortunately the average of the
sum of the column lengths is only 0.08 different... so I still stuck
at a rounded up value of 57 from the formula.
Received on Wed Apr 04 2007 - 06:41:09 CDT
