Re: Maximum Extent size
mchivaviro@okzim.co.zw wrote: > I am using IDS 7.31 and I have a big table which is now 22gigs in size. > It now has 60 extents and I need to unload and recreate the table > again. What is the maximum extent size I can create and the next size > of the extent. Is the performance degrading? If not, do nothing. My own take on this now is that busy tables tend to end up in the buffers, so the fragmentation isn't an issue. Tables (or parts of tables) which are not busy will always be relatively slow as they have to be read from disk. However I'm sure someone will disagree with me! The one time I had to worry about the number of extents was because the table had reached the maximum allowed and inserts were failing.
To add: What "ends up in the buffers and gets preference to stay in the buffers" varies with IDS release. In IDS 7.3, we gave preference (via hard-coding or priorities) to index pages (different priorities based on the type of page) and memory resident pages (set explicitly). So - data pages could end up thrashing a great deal as they kept getting kicked out (replaced) with pages that had a higher priority. Large clients ended up with index pages consuming or dominating the buffer cache, therefore data pages were predominantly always being retrieved from disk. In IDS 9.4+, we rewrote the buffer manager algorithm to NOT hard-code the page priorities, but give preference to "popular pages." The short explanation is that "popular pages stick around", and just low usage pages are candidates for replacement. But - I would never rule out fragmentation of large tables. It continues to be a great solution for very large tables or indexes. BTW - with respect to the "how large of an extent can I create"...remember that with IDS a "EXTENT SIZE <K>" during a table creation is a 'suggestion', not a 'demand.' IF there is not <K> contiguous pages available, we'll give you the most we can (that explanation I will save due to length). I believe in Oracle, it is a demand (it fails if the engine can't get that space)? Can some of you O folks verify this? HTH - Mark Scranton Xtivia Inc. Informix 1995-2005 Cats wrote: > mchivaviro@okzim.co.zw wrote: > > I am using IDS 7.31 and I have a big table which is now 22gigs in size. > > It now has 60 extents and I need to unload and recreate the table > > again. What is the maximum extent size I can create and the next size > > of the extent. > > Is the performance degrading? If not, do nothing. My own take on this > now is that busy tables tend to end up in the buffers, so the > fragmentation isn't an issue. Tables (or parts of tables) which are > not busy will always be relatively slow as they have to be read from > disk. However I'm sure someone will disagree with me! The one time I > had to worry about the number of extents was because the table had > reached the maximum allowed and inserts were failing.