Re: Help: Settle an argument regarding EXTENTS

Allan Kelly wrote:
>
> We currently have a development project in progress. The whole application
> revolves around an Oracle 7.3.4 database. The users have told us that they
> anticipate having roughly 100 GB of data that will have to be archived off
> regularly after reaching this 100 GB high-water mark after 6 months of use.
> There are approximately 120 tables in the main schema. One of which, will
> hold approximately 57-60 GB of data when the high-water mark is reached and
> weekly archival begins (at the six month mark).
>
> My problem is: we have a contractor DBA hired to manage the database and who
> stubbornly insists on allocating the complete storage size each table will
> occupy after six months in the INITIAL EXTENT. Example: for the main table
> described above, he wants to build the table with INITIAL set to 60 GB and
> NEXT set to 1.5 GB. He insists on building the other tables the same way.
>
> Knowing Oracle as I do, I find this more than a little disturbing. However,
> this DBA insists that "that's the way he's always built tables", with no
> other explanation or facts to back up his assertion.
>
> Trying to keep an open mind here, I am asking: Is there any benefit to this
> scheme he proposes? Let me hear your pro's and con's, please.
>
> Allan

If i understood you correctly, all tables grow to some more or less constant size, are archived and then start empty and so on. In this context, the suggestion to rebuild the tables with an initial extent large enough to hold all of the (expected) data, i.e. about what the contained before saving the contents, seems to be a good idea: if not, during growing, each table may allocate new extents and will be fragmented. If you set up an initial extent of the "required" size, the dbms has at least a chance to allocate all of this in one contiguous extent. btw: this also applies to indices ! On the other side, as far as i remember, large, nearly empty extents have heavy performance-disadvantages whenever full-table-scans are executed. But it's very hard to find out if this applies to your special situation. I also do not know if this is correct for tables only or for indices, too.

I hope this is a little clearer than "that's the way he's always built tables".

Arnold Schommer Received on Tue Mar 30 1999 - 13:29:42 CEST