Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: average row length for a table
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
-- =================================================================== Brian Peasland dba_at_nospam.peasland.net http://www.peasland.net Remove the "nospam." from the email address to email me. "I can give it to you cheap, quick, and good. Now pick two out of the three" - Unknown -- Posted via a free Usenet account from http://www.teranews.comReceived on Tue Apr 03 2007 - 15:13:50 CDT