Table Sizes

[DQuinn]

Hi all,

I was wondering...
I have to set up a new database. I know my row sizes and approximate number of records. Depending on the growth rate of my tables, is it better to have less extents (but bigger), or more extents (but smaller).

My personal idea (could be wrong) is :

For tables that will not grow much, a bigger extent
For tables that will grow often, more smaller extents.

Is this O.K. ??
Any better suggestions??

BTW....It is under Oracle 8.1.7

Thanks

[macswell]

Hi -

As with everything else, it's a question of trade-offs. In general, allocating extents is I/O (therefore, slow) and should be minimized. Balancing this is the need to effectively utilize your disk space. Since the disk space for new extents must be contiguous, small extents can be allocated more easily than larger extents, and reduce the chance of having a fragmented tablespace with many small unuseable segments. You can run into situations where you have fragmented space available, but not enough contiguous to allocate for a new large extent. As an aside, I like to allocate the "initial" extent for each segment to hold all of the data for that segment. Also, remember to allocate extents in multiples of your database (not OS) block size to minimze the occurence of small, unuseable spaces in your tablespace.

My preference is to use small extents for infrequently used tables (to fill the "holes"), and large extents for frequently used tables. I then monitor segments on a monthly basis (your mileage may vary) to find those which have grown past a certain threshhold (10 extents). I then drop that segment, rebuild it with a larger initial extent, and resize the next extent as appropriate. I also monitor tablespace fragmentation on a monthly basis, and rebuild the entire tablespace as needed (which is a pain, but infrequent).

My 2 cents worth.

Tim

[DQuinn]

Thanks Tim.

I appreciate the comments.