initial and next extent

[diwakar]

Dear Sir,


Is it advisable for the size of the initial and next extent size of the table to coincide with the values given for the initial and next extents of the corresponding tablespace?? If so may I know the reason.

Thanks and Regards,

K.Diwakar

[anandkl]

Its not necessary for the initial and next extent of table to concide with the tablespace.It would be good if you set ur initial and next extent storage parameter at the table level rather than the tablespace level to avoid fragmentation and the storage parameter set at the table level will over ride the tablespace level parameters...


regards
anandkl

[pando]

Originally posted by anandkl
set ur initial and next extent storage parameter at the table level rather than the tablespace level to avoid

in fact by doing that you will cause fragmentation, the ideal is dont set anything at segment level so all extents share same size (assumming tablespace is created with same initial and next size) which is what LMT can achieve with uniform size clause

[anandkl]

I was talking for tablespace which are not LMT and u need to set ur initial and next of equal size at the table level...which will avoid fragmentation...

regards
anandkl

[pando]

in DMT what you do is set minimum extent clause when you create your tablespace so even you set different extent size they will be multiple of each other and can be resued more easily (reducing fragmentation)

[nagarjuna]

The strategy is like this.
1. Know the extent sizes of typical segments that are going to be created on a particular tablespace.
2. Use these values to create the tablespace.
3. These default values can be used for typical segments (Tables, Indexes etc).
4. User defined extent sizes can be used if the new segment is going to behave differently ( very small or large size)

I guess, this slves the debate of whether to use tablespace defaults or at segment level values.

To come to the base question...

It is advisable to have size of initial and next extents for tablespaces. The same applies for the newly created tables too.
If the table is created using a direct inserts and expected to be of bigger size, then we should go for a bigger intial extent that is multiple of next extent. This gives minimal ammount of fragmentation.

To remind you guys... The thumb rule is to have the extent sizes as multiples of "db_file_multiblock_read_count"

Offourse, LMT (locally managed tablespace) solves the problem of fragmentation. But, the bulk inserts are slower in LMT compared with DMT (dictionary managed tablespace).

So, it is upto we DBAs to chose the righ extent size and tablespace type to suit our requirements and needs.. This is called human touch...

-nagarjuna

[diwakar]

Dear all,

Thanks for your answers. But my question is like this : say you want to set the size of initial and next extent of a table to a value say 'x' (value is chosen depending on the growth you expect), Is there any advantage of this value being a multiple of the size of initial and next extent of the corresponding tablespace.

Regards,

K.Diwakar

[Edited by diwakar on 08-30-2002 at 05:08 AM]

[jmodic]

Originally posted by nagarjuna
But, the bulk inserts are slower in LMT compared with DMT (dictionary managed tablespace).

How did you come to that conclusion? Can you elaborate this a bit further?

[sandycrab]

quote:
--------------------------------------------------------------------------------
Originally posted by diwakar
Is there any advantage of this value being a multiple of the size of initial and next extent of the corresponding tablespace.

--------------------------------------------------------------------------------

It can be advantageous as you'll face lesser disk fragmentation. But it is advisable that if you need to specify intial and next extent at table level, don't give a option for the same while creating tablespace.
Plz correct me if I'm wrong.
Sandy

[diwakar]

Originally posted by sandycrab
quote:
--------------------------------------------------------------------------------
Originally posted by diwakar
Is there any advantage of this value being a multiple of the size of initial and next extent of the corresponding tablespace.

--------------------------------------------------------------------------------

It can be advantageous as you'll face lesser disk fragmentation. But it is advisable that if you need to specify intial and next extent at table level, don't give a option for the same while creating tablespace.
Plz correct me if I'm wrong.
Sandy

Thank you for your answer but could you tell me how this could cause lesser fragmentation.

Regards,

K.Diwakar