Re: Table size

In article <3ADBFFDB.50B90C55_at_prism.uvsq.fr>, Fei Sha <Fei.Sha_at_prism.uvsq.fr> writes:
>Hi,
> How to compute the disk space allocated to a table and how to
>estimate
>the size of an index?
>
>Thanks!
>
>Fei
>

Oracle Table Sizing Estimation 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 --
  • Posted via NewsOne.Net: Free (anonymous) Usenet News via the Web ----- http://newsone.net/ -- Free reading and anonymous posting to 60,000+ groups NewsOne.Net prohibits users from posting spam. If this or other posts made through NewsOne.Net violate posting guidelines, email abuse_at_newsone.net