How to estimate table size 1

[StevePB]

This sounds like it should be a simple task, but I have not been able to find a simple explanation.

I need to create a new table, which will grow to be quite large (approx. 500,000 records). I want to calculate how many pages to assign to the table at creation, and what the next extent size should be.

I know approx. how many records, and columns, and the datatypes of the columns. The create table statement is as follows:
[tt]
CREATE TABLE drsstatsumm
(
stat_yr decimal(2,0),
district_major decimal(10,0),
st_loc decimal(2,0),
art_no char(14),
pur_man_cd decimal(1,0),
ord_qty decimal(14,3),
ord_revenue decimal(12,2),
ord_cost decimal(12,2)
)
IN #>> dbspace ?
EXTENT SIZE #>> size ?
NEXT SIZE #>> size ?
;
[/tt]
Can anyone provide me with a step-by-step calculation ?

Thanks in advance - SteveB.

 

[vpshriyan]

Hi Steve,

The rowsize for your table specification works out to 49 Bytes approx.

Assuming you have box with page size configured as 2K:
Data rowsize = 49 + 4 (slot table entry size) = 53
Available space in a page = 2048 - 28 (page overhead) = 2020
Number of rows per page = 2020 / 53 = 38.1 --> 38 rows

Assuming you have around 150,000 rows of data ready to insert, and you would expect around 100,000 number of rows would be added in immediate future:
FIRST EXTENT: 150,000/38 = 3947.3 --> 3947 pages
NEXT EXTENT: 100,000/38 = 2631.5 --> 2632 pages

DB Space required in Kb for extents
FIRST EXTENT: 3947 * 2 Kb = 7894 Kb
NEXT EXTENT: 2632 * 2 Kb = 5264 Kb

Hence, when the table is populated say, with 500,000 rows over a period of time, it would have approximately 5 extents in all.

Regards,
Shriyan
"Don't discourage another's plans unless you have better ones to offer.

 

[StevePB]

Shriyan, thanks very much, that's just what I needed.

Cheers, SteveB.