| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> 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_at_brk.photronics.com
Home Number 203.270.8159
Alpha Pager mailto:spalmer.pager_at_brk.photronics.com
--------------48D27DB45B00CDC3A86801DD Content-Type: text/html; charset=us-ascii Content-Transfer-Encoding: 7bit
<HTML>
Thanks to all who helped out... The question was "<B><I><FONT COLOR="#FF0000">How
could I figure out the</FONT></I></B>
<BR><B><I><FONT COLOR="#FF0000">average row size of a table</FONT></I></B>".
Most responses was to analyze the table and
<BR>select AVG_ROW_LENGTH from USER_TABLES.
<P>ANALYZE TABLE xxx ESTIMATE STATISTICS;
<P>SELECT TABLE_NAME, AVG_ROW_LEN <BR>FROM USER_TABLES <BR>WHERE TABLE_NAME = 'xxx'; <P>I also found the following on MetaLink....... <BLOCKQUOTE TYPE=CITE>SQL> select AVG(NVL(VSIZE(ALTL_ID),1 )) + <BR> AVG(NVL(VSIZE(JLN_ID_FK),1 )) + <BR> AVG(NVL(VSIZE(EQP_ID_FK),1 )) + <BR> AVG(NVL(VSIZE(WKC_ID_FK),1 )) + <BR> AVG(NVL(VSIZE(PREFERRED),1 )) +<BR> AVG(NVL(VSIZE(LAST_UPDATE_DATE_TIME),1 )) + <BR> AVG(NVL(VSIZE(LAST_UPDATE_EMP_ID_FK),1 )) "SPACE OF AVERAGE ROW"
<P>SPACE OF AVERAGE ROW <BR>-------------------- <BR> 25.490595</BLOCKQUOTE>Of course... the two dont agree........ :-)
<P>Steve
<BLOCKQUOTE TYPE=CITE>On Mon, 22 May 2000, Stephen J Palmer wrote:
<P>> Hello all,
<BR>> I need to figure out what the average row size of a table is. I'm
sure I
<BR>> have come
<BR>> across this a while back, but have been unable to locate it from
my
<BR>> notes. <BR>> <BR>> Any help with this would be greatly appreciated. <BR>> <BR>> Thanks in advance.</BLOCKQUOTE>
<BR>Junior Database Administrator <BR>Photronics Inc. <BR>15 Secor Road <BR>Brookfield, Conn. <BR>203.740.5331 <BR>Pager 203-830-0306 or 1-800-706-7109 <BR><A HREF="mailto:spalmer_at_brk.photronics.com">mailto:spalmer_at_brk.photronics.com</A> <BR>Home Number 203.270.8159 <BR>Alpha Pager <A HREF="mailto:spalmer.pager_at_brk.photronics.com">mailto:spalmer.pager_at_brk.photronics.com</A></HTML>Received on Mon May 22 2000 - 20:01:13 CDT
 |
 |