Re: Re:AVG_ROW_LEN

From: Ed Bittel <ebittel_at_yahoo.com>
Date: Fri, 7 Jul 2000 15:42:06 -0700
Message-Id: <10551.111479@fatcity.com>

-----Original Message-----
From: dgoulet_at_vicr.com <dgoulet_at_vicr.com> Subject: Re:AVG_ROW_LEN

> I do not believe that the AVG_ROW_LEN column contains any thing other
than
>the actual average row length. At least comparisons of the
>avg(vsize()+vsize()...) and AVG_ROW_LEN bear that out.

Thanks for resonding, however I tested it after I sent the message (I know, wrong order). My test (8.0.5) suggests that AVG_ROW_LEN includes both the byte length overhead and the row header overhead. Besides, I read that AVG_ROW_LEN includes overhead in an Oracle document, so it must be true ;o). The document didn't specify if both the row header + and column overhead were incuded, which is why I asked the question.

EXAMPLE:

---

oradba> create table test_space (test1 number, test2 varchar2(10));

Table created.

oradba> insert into test_space values (1234.99, 'Go Crew!');

1 row created.

oradba> analyze table test_space compute statistics;

Table analyzed.

oradba> select avg(vsize(test1)+vsize(test2)) from test_space;

AVG(VSIZE(TEST1)+VSIZE(TEST2))

---

                            12

oradba> select avg_row_len from dba_tables
  2     where table_name='TEST_SPACE';

AVG_ROW_LEN

---

         17

   4 bytes for the number in TEST1

+ 8 bytes for 'Go Crew!' in TEST2
+ 3 bytes for the row header
+ 2 bytes for the byte length overhead
=============================

 17 bytes for the data + overhead

---

Do You Yahoo!? Received on Fri Jul 07 2000 - 17:42:06 CDT