Re: Table Sizing Estimation

If you have some data there already, analyze on the table, then check the stats.

sp wrote:

> Or you can use T.O.A.D.
> HTH
> Sp
> "Mark D Powell" <markp7832_at_my-deja.com> wrote in message
> news:8uh8d0$6r4$1_at_nnrp1.deja.com...
> > In article <3A0B6D89.757F00C4_at_hket.com>,
> > Rayfil Cheung <rayfilcheung_at_hket.com> wrote:
> > > Hi,
> > > How I estimate the table sizing when certain no. of rows are expected.
> > > Thanks.
> > >
> > There are several ways to do this. Here is a fairly quick formula.
> >
> > Abbreviations
> >
> > AVIL = Available space in block to hold rows
> > OBS = Oracle block size
> > RS = Row size
> > Ovhd = Fixed plus variable block overhead
> > TBR = Total blocks required
> >
> > Expected size = (( RS * number of rows) / AVIL ) * OBS) / K or M
> > where K = 1024 and M = 1048576
> >
> > Figure RS as
> > for varchar2 expected number of characters for column
> > for number 1 + floor(num digits / 2) + 1
> > for date use 7
> > + 1 byte per column in row
> > + 3 byte row overhead per row
> >
> > Figure number of bytes for block as
> > pctfree = decimal value of pctfree parameter * OBS
> >
> > The variable area is mostly made up of 23 bytes per initran area and 2
> > bytes per row for the row table entry. For 1 to 4 initrans I have
> > calculated row overhead of 86 to 156 bytes so I just use a constant for
> > this value. Try 113 to start.
> >
> > Figure AVIL as OBS - ovhd - pctfree
> >
> > Total bytes = number of expected rows * RS
> > TBR = Total Bytes / AVIL
> > Expected Size = TBR * OBS / 1024 [for K]
> >
> > This is one way and it is fairly quick and works pretty well. The
> > formula can be improved by adjusting the variable area size for the
> > number of initrans and for the number of expected rows in the block,
> > but using a constant works well for us.
> >
> > --
> > Mark D. Powell -- The only advice that counts is the advice that
> > you follow so follow your own advice --
> >
> >
> > Sent via Deja.com http://www.deja.com/
> > Before you buy.
Received on Sun Nov 19 2000 - 01:31:01 CET