Message-Id: <10505.106302@fatcity.com> From: "Stephen J Palmer" Date: Mon, 22 May 2000 21:01:13 -0400 Subject: Re: Average row size ques... --------------48D27DB45B00CDC3A86801DD Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit Thanks to all who helped out... The question was "How could I figure out the average row size of a table". Most responses was to analyze the table and select AVG_ROW_LENGTH from USER_TABLES. ANALYZE TABLE xxx ESTIMATE STATISTICS; SELECT TABLE_NAME, AVG_ROW_LEN FROM USER_TABLES WHERE TABLE_NAME = 'xxx'; I also found the following on MetaLink....... > SQL> select AVG(NVL(VSIZE(ALTL_ID),1 )) + > AVG(NVL(VSIZE(JLN_ID_FK),1 )) + > AVG(NVL(VSIZE(EQP_ID_FK),1 )) + > AVG(NVL(VSIZE(WKC_ID_FK),1 )) + > AVG(NVL(VSIZE(PREFERRED),1 )) + > AVG(NVL(VSIZE(LAST_UPDATE_DATE_TIME),1 )) + > AVG(NVL(VSIZE(LAST_UPDATE_EMP_ID_FK),1 )) "SPACE OF AVERAGE ROW" > from allowable_tools; > > SPACE OF AVERAGE ROW > -------------------- > 25.490595 Of course... the two dont agree........ :-) Steve > On Mon, 22 May 2000, Stephen J Palmer wrote: > > > Hello all, > > I need to figure out what the average row size of a table is. I'm sure I > > have come > > across this a while back, but have been unable to locate it from my > > notes. > > > > Any help with this would be greatly appreciated. > > > > Thanks in advance. Stephen Palmer Junior Database Administrator Photronics Inc. 15 Secor Road Brookfield, Conn. 203.740.5331 Pager 203-830-0306 or 1-800-706-7109 mailto:spalmer@brk.photronics.com Home Number 203.270.8159 Alpha Pager mailto:spalmer.pager@brk.photronics.com --------------48D27DB45B00CDC3A86801DD Content-Type: text/html; charset=us-ascii Content-Transfer-Encoding: 7bit Thanks to all who helped out... The question was "How could I figure out the
average row size of a table". Most responses was to analyze the table and
select AVG_ROW_LENGTH from USER_TABLES.

ANALYZE TABLE xxx ESTIMATE STATISTICS;

SELECT TABLE_NAME, AVG_ROW_LEN
FROM USER_TABLES
WHERE TABLE_NAME = 'xxx';

I also found the following on MetaLink.......

SQL> select AVG(NVL(VSIZE(ALTL_ID),1 )) +
    AVG(NVL(VSIZE(JLN_ID_FK),1 )) +
    AVG(NVL(VSIZE(EQP_ID_FK),1 )) +
    AVG(NVL(VSIZE(WKC_ID_FK),1 )) +
    AVG(NVL(VSIZE(PREFERRED),1 )) +
    AVG(NVL(VSIZE(LAST_UPDATE_DATE_TIME),1 )) +
    AVG(NVL(VSIZE(LAST_UPDATE_EMP_ID_FK),1 )) "SPACE OF AVERAGE ROW"
    from allowable_tools;
SPACE OF AVERAGE ROW
--------------------
           25.490595

Of course... the two dont agree........ :-)

Steve

On Mon, 22 May 2000, Stephen J Palmer wrote:
> Hello all,
> I need to figure out what the average row size of a table is. I'm sure I
> have come
> across this a while back, but have been unable to locate it from my
> notes.
>
> Any help with this would be greatly appreciated.
>
> Thanks in advance.

Stephen Palmer
Junior Database Administrator
Photronics Inc.
15 Secor Road
Brookfield, Conn.
203.740.5331
Pager 203-830-0306 or 1-800-706-7109
mailto:spalmer@brk.photronics.com
Home Number 203.270.8159
Alpha Pager mailto:spalmer.pager@brk.photronics.com