Re: table size

"G. Napol" <napol_at_libero.it> wrote:
>Ciao Corrado
>
>You can use the 'ANALYZA TABLE table_name COMPUTE STATISTICS'
 statement
>and then read the computed statistics from the dictionary tables
>USER_TABLES, ALL_TABLES or DBA_TABLES.
>
>Hope this helps
>
>Ciao Gennaro
>
>corrado brambilla wrote:
>
>> Hi, I need to calculate a table size for any tables in my db.
>> Please advice how to do so.
>>
>> Thanks
>> Corrado
>>

Ciao, I think you gave Corrado a good starting point but I think we should carry the process forward and say what to do with the numbers to create an estimate for total table size.

If decent data exists use the statistics for the average row size and an estimage for the number of rows, but data does not exist to work from, take the table description and estimate a table row length using 7 bytes for date columns, the actual number of expected characters for varchar2, and 1 + floor(no of digits to hold value / 2) + 1 for numbers, plus add 3 bytes to every row for row overhead.

Here is a formula:
erc estimated row count
ers estimated row size
blk oracle block size
ovh estimated oracle block overhead

(((erc * ers) / (blk - ovh - pctfree)) * blk ) / 1024 = size K

for ovh rather than calculate it you might just want to use a constant like 150 for the fixed overhead plus variable overhead

and since your Oracle block size is fixed for the db you might as well use 10% as your pctfree then you get a constant like 7238 (8192 - 150 - 402) for an 8K block. For the great majority of tables this will be close enough.

This makes the fromula erc X ers / 7238. After performing this calculation you have the total number of Oracle blocks necessary to hold the data so multiple by the block size to get the total bytes necessary. Next divide by 1024 to convert the size to Kilobytes and divide once again to get Megabytes if desired.

50,000 rows * 100 bytes ea / (7238) * 8192 = 5000000/7238 = 691 [round up] * 8192 = 5660672 / 1024 = 5525K or 5.3M

Depending on your space management policy you might want to allocate this table expecting 6 one meg extents, 2 five meg extents, or 1 ten meg extent etc....

I hope this helps and I do not guarentee the math above, but this formula has worked reasonably well for our team. The problem is usually with estimating how many rows will be created over the next year and how many years of data will be held before a purge is performed.

Got questions? Get answers over the phone at Keen.com. Up to 100 minutes free!
http://www.keen.com Received on Thu Jul 13 2000 - 00:00:00 CDT