Path: news.f.de.plusline.net!news-fra1.dfn.de!news.visyn.net!news2.arglkargh.de!news.osn.de!diablo1.news.osn.de!newsfeed.freenet.de!border2.nntp.ams.giganews.com!nntp.giganews.com!newsfeed101.telia.com!nf02.dk.telia.net!out04b.usenetserver.com!news.usenetserver.com!in02.usenetserver.com!news.usenetserver.com!postnews.google.com!d57g2000hsg.googlegroups.com!not-for-mail
From: "MTNorman" <mtnorman@duke-energy.com>
Newsgroups: comp.databases.oracle.server
Subject: Re: average row length for a table
Date: 4 Apr 2007 04:41:09 -0700
Organization: http://groups.google.com
Lines: 30
Message-ID: <1175686869.629379.73700@d57g2000hsg.googlegroups.com>
References: <1175623636.920607.146360@w1g2000hsg.googlegroups.com>
   <4612a919$0$16390$88260bb3@free.teranews.com>
NNTP-Posting-Host: 148.134.37.3
Mime-Version: 1.0
Content-Type: text/plain; charset="iso-8859-1"
X-Trace: posting.google.com 1175686871 27527 127.0.0.1 (4 Apr 2007 11:41:11 GMT)
X-Complaints-To: groups-abuse@google.com
NNTP-Posting-Date: Wed, 4 Apr 2007 11:41:11 +0000 (UTC)
In-Reply-To: <4612a919$0$16390$88260bb3@free.teranews.com>
User-Agent: G2/1.0
X-HTTP-UserAgent: Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1; SV1; .NET CLR 1.1.4322; .NET CLR 2.0.50727; InfoPath.1),gzip(gfe),gzip(gfe)
Complaints-To: groups-abuse@google.com
Injection-Info: d57g2000hsg.googlegroups.com; posting-host=148.134.37.3;
   posting-account=R9Xy5Q0AAACBfoRETPNOB28HQN4N2LNQ
Xref: news.f.de.plusline.net comp.databases.oracle.server:195727

On Apr 3, 4:13 pm, Brian Peasland <d...@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.