Re: Fragmenation Question

What you're trying to avoid in this kind of case is records in overflow because that takes more I/O and so slows things down.

How much free space you leave on a page depends almost entirely on your update/insert patterns.

In this case as long as your key design is good and the inserts are spread evenly across your data - if your table will take 10 years to double in size 50 percent empty pages will last you ten years (and process times will be slower but consistent) - but of course it will cost you twice the space. etc.

Another approach for this type of table might be just to wait until overflow degrades performance to an unacceptable level the export the table and reload it.

So as ever it all depends.

Allan

<jgonzalezsNO_at_SPAMoviedo.syseca.es> wrote in message news:bJ55OI0HN0bPafA00Tnicb3zPj5W_at_4ax.com...
> On Wed, 05 Jan 2000 05:55:00 GMT, dmausner_at_ameritech.x.net (dave
> mausner) wrote:
>
> >a larger pctfree (then the default of 10) doesn't mean more fragmentation
> >on the initial load; it just means that the table will initially be
larger
> >or have more extents or have a higher high-watermark. fragmentation is
bad
> >because logically adjacent rows are physically far apart. that doesn't
> >occur just because pctfree > 10. rest assured the db will still be clean.
> >
> >if you set pctfree=50 (for example) the data will be more spread out and
> >occupy more blocks. a full scan of the table visits more blocks and does
> >twice the i/o, as the same data loaded with pctfree=0.
>
> So, What's the *best* settings for pctfree and pctused params for a
> table of historical data, which you insert data along time but never
> update or delete?
> (I've never touched this params when creating a table because I don't
> understand completely their meaning).
>
> Thanks.
>
> jgonzalezsNO_at_SPAMoviedo.syseca.es
> (remove NO and SPAM)
Received on Mon Jan 10 2000 - 04:53:24 CST