Re: fragmentation issues

"yls177" <yls177_at_hotmail.com> wrote in message news:c06e4d68.0405281858.778abb5f_at_posting.google.com...
> 1) using locally managed tablespaces with uniform extent sizes means
> that it is physically impossible to have fragmentation.
>
> 2) using dictionary managed tablespaces but create all objects
> with initial=next and pctincrease=0 and keep all same sized objects in
> a
> tablespace together, fragmentation is minimized
>
>
> (1) is very clearcut... but my conerns are with (2) since right now, i
> have huge databases around 1.5GB that are on DMT. My thoughts are that
> it is easy to keep the initial=next and pctincrease=0. However, to
> keep all same sized objects in a tablespace will be tough... that will
> require us to have a foreknowledge of these objects beforehand...
> right.. but then arent we just mere human under the guidance of a
> almighty God.....
>
>
> so my proposed solution is that since the initial=next is there...
> that means that all the extent sizes are the same........ and that
> will do the trick... isnt it?
>
> thanks

Nope, unfortunately. Because whoever creates the segments stored within the tablespaces can specify their own, operative, storage clause. And the one you specify for the tablespace is only a *default* storage. And a default is only used when the segment doesn't come to the party with its own storage clause.

Which is a roundabout way of saying that there is nothing you can do to guarantee initial=next in DMT, unless you can be absolutely sure that no 'create table...storage(initial=X next=Z)' commands have snuck in under the radar.

For DMT, you can *try* to help things along by specifying MINIMUM EXTENT for a tablespace. That is not part of the default storage clause, and therefore cannot be overridden by anything in the segment's specific storage clause. However, it still doesn't do the job, because it is *interpreted* in the light of a segment-specific storage clause. If minimum extent is 500K, and your segment requests an initial of 23K, it will be given 500K. But if the segment then asks for a next of 612K, it will be given a 1MB extent... at which point, initial does not equal next for that tablespace. It certainly means you have less variation in extent sizes, however, and is therefore the next best thing to using LMTs.

You can retrofit a DMT tablespace with a MINIIMUM EXTENT clause (alter tablespace X minimum extent Y). But that doesn't do anything for the extents within the tablespace that have already been allocated, of course.

Regards
HJR Received on Fri May 28 2004 - 22:36:35 CDT