Creating tables -- values for storage clause params

[bobby1977]

Could anyone tell me if there is a formula which i could use to give proper values ( in order to increase performance ) to parameters used in the "storage" clause from "create table" ?
The params which I consider are:
INITIAL, NEXT , MINEXTENTS, MAXEXTENTS, PCTINCREASE,
FREELISTS, FREELIST GROUPS, BUFFER_POOL

Thankx !

 

[MikeJones]

I'm not really an expert on this, a DBA would be better but as a developer I just can't help myself....

You need to size your table correctly, this will enable to to put the correct storage values on it. You need to ask the following questions.

1. How big is 1 row when full up typical data.
2. How big is 1 row when inserted (Typically)
3. How volatile is this data (i.e. inserts/updates/deletes)

Then I do the following...

Firstly and most importantly I only ever set Initial, next and pctincrease.

Secondly I alsways set initial and next as the same and the pctincrease as 0. All objects in this tablespace have the same values (initial next). The reason for this (I've been told!) is that the tablespace then gets consumed in uniform chucks and coalesces are very quick and free up ALL spare space in the tablespace.

Next in order to work out the size for initial and next I work out the typical row size, a bit of knowledge about your data and some common sense is needed here. Then ask your self how many rows will be inserted in a day and make sure that you can stick that in one extent.

I typically have 3 types of tablespace, one where the extents are 500K, one where they are 2 MB and and optional one if the DB has very large tables where the chuck size is 10M

HTH,

Mike.