Re: Fragmenation Question

In article <bJ55OI0HN0bPafA00Tnicb3zPj5W_at_4ax.com>,   jgonzalezsNO_at_SPAMoviedo.syseca.es wrote:
> 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.
>

If the history data is never updated after insertion then in theory a pctfree of zero would work fine; however, when you add a new column to the base table and then add it to the history table you will have no room to hold the column so I always leave at least 5 percent pctfree to accomdate things like this.

There is a formula in the manual, either Concepts or DBA Admin, on setting pctfree and pctused. But the pctfree setting value relies on the DBA or developer to know how the data will be inserted and updated and to make a judgement call.

The ver 8 DBA manual covers pctfree and pctused in Ch 10. --
Mark D. Powell -- The only advice that counts is the advice that  you follow so follow your own advice --

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Mon Jan 10 2000 - 11:42:14 CST