Re: Why is NEXT_EXTENT changing all the time?

Niall Litchfield wrote:
>
> Well now (and hoping that I am reasonable and that the wind is in the west)
>
> I disagree strongly with this suggestion.
>
> For me ALL segments within a tablespace should have an identical extent
> size, and each segment should be allocated to a tablespace with an
> appropriate storage clause. This way a table which wants to grab a next
> extent can always do it (providing the tablespace is not full). In other
> words fragmentation is eliminated.
>
> I utterly fail to see why one would size segments at different sizes, thus
> guaranteeing fragmentation, merely in order to hold one to three years worth
> of data. The only possible 'justification' would be in an environment when
> yearly reviews of database operation were the object. That translates to
> consultancy.
>

The reason it happens is that there are hundreds of books about Oracle that all make the recommendation that tables be sized based on their logical usage. Some of the books even provide complex formulas for figuring this out. However, after reading the white papaer that was referenced in this thread I am going to start changing the way I do things and switch to a data-independent physical structure where I use a single extent size in any given tablespace.

Just some history though. In the earlier versions of Oracle (especially Oracle 6) it was part of conventional wisdom that all data should be placed in a single extent if at all possible because this would reduce access time for a discrete row. In addition, versions prior to 7.3 had strict limits on how many extents an object could have depending on the size of the database block - and since the default block size was 2048, this means that the largest table could only have 121 extents so the DBA was virtually forced to play around with extent sizes if they wanted to have large tables. But, now that all of these restrictions have been removed in recent releases we can start using a strategy that makes sense rather than being forced to juggle things around because of limitations of the database software. Received on Thu Jan 25 2001 - 09:36:39 CST