Re: fragmentation issues

"Howard J. Rogers" <hjr_at_dizwell.com> wrote in message news:<40b80537$0$31675$afc38c87_at_news.optusnet.com.au>...
> "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.

after the segment requests for an initial of 23k, 377k still remains in the extent. if another new segment requests for 70K, will it use the same segment as the 23K or a total new extent will be given?

also, a 1MB extent is given because the new segment is asking for a 612K, whereas the minimum extent size is 500K, therefore it is not sufficient and hence 2 is needed or 1 extent with a size of 1MB? . which means to say that the extent size are not uniform which is fragmentation
>
> 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.
>

here, u were saying that the above sql command is not valid for those extents that have already been allocated?
> Regards
> HJR
Received on Sat May 29 2004 - 08:10:46 CDT