Table Sizes
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

Thread: Table Sizes

  1. #1
    Join Date
    Mar 2001
    Posts
    82

    Cool

    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

  2. #2
    Join Date
    Sep 2000
    Posts
    47
    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


  3. #3
    Join Date
    Mar 2001
    Posts
    82
    Thanks Tim.

    I appreciate the comments.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width