Re: Fragmentation in Oracle

Try using an 80% (or higher) PCTUSED setting on the table. Please see below.

Also beware, Index space IS NEVER REUSED. If you add records, then delete them, the index space is not release nor reused.

Also ensure that indexes are on separate tablespaces and if this table really fragments (because of other objects in the tablespace), you might what to consider putting it on it's own tablespace.

Some Info From Oracle's Manuals:
"PCTUSED
specifies the minimum percentage of used space that Oracle7 maintains for each data block of the table. A block becomes a candidate for row insertion when its used space falls below PCTUSED. PCTUSED is specified as a positive integer from 1 to 99 and defaults to 40.

PCTUSED has the same function in the commands that create and alter clusters, snapshots, and snapshot logs.

The sum of PCTFREE and PCTUSED must be less than 100. You can use PCTFREE and PCTUSED together use space within a table more efficiently. For information on the performance effects of different values PCTUSED and PCTFREE, see Oracle7 Server Tuning."

Dave

Jorge A. San Martín wrote in message <3461E97B.CF3F8FB6_at_tortuga.its.cl>...
>Hi,
>I'm using Oracle 7.3.2.3 on an Alpha with OSF/1 v4.0, and I have some
>questions.
>First, fragmentation problem exist?
>Second, I have a process what makes "depuration", it does not delete all
>the records from a table, just the ones that meet a condition, I
>suppose that this operation makes some fragmentation, how can I fix
>this? I'm thinking of making an export of the table, truncate it and
>import it, but this is very ineficient, we are calculating 1,000,000
>records a day :(
>What would you recomend?
>
>That is all, thanks,
>
> Jorge San Martin
>
>
>
>
>
Received on Thu Nov 06 1997 - 00:00:00 CET