Re: Defrag Tablespaces and pctincrease 1

"Lun Wing San (Oracle)" <wslun_at_qrcsun.qrc.org> wrote:

>Scott T. Johnson wrote:
>>
>> I am creating a new database and would like to create tablespaces
>> that are easy to defrag. I once read that setting pctincrease to 1 in the
>> storage clause when creating the tablespace will have Oracle automatically
>> defrag the tablespaces.
>>
>> Is there any truth to this? What are the drawbacks?
>
> Yes. You can defrag it by setting pctincrease greater than 0. However, if you set the
>pctincrease too high, you will be difficult to control the growth rate of tablespace if
>you use autoextend on option. Moreover, you will be difficult to control the growth rate
>of data segments which rely on the default values provided by the storage settings of
>tablespaces.

Setting the default PCTINCREASE > 0 will not defrag a tablespace. It will allow automatic coalescing. Coalescing simply joins adjacent free spaces together into one large one. Defragmenting involves the actual moving of segments.

For example, suppose you have a tablespace with two tables and two free spaces in the following order.

1. Table1 2m
2. Free1 1m
3. Free2 3m
4. Table2 4m

Coalescing (done on demand by pctincrease > 1, or with ALTER TABLESPACE xxx COALESCE;) will only merge Free1 and Free2 into a single freespace of 4m.

Defragmenting will put table2 immediately after table1, and create a free space at the end that extends all the way to the end of the tablespace.

--
Chuck Hamilton
chuckh_at_dvol.com

This message delivered by electronic sled dogs. WOOF!