Re: What happens when a table has over 200 extents?

@access1.digex.net (Lester Knutsen) writes: |> I have a site with a very large and fragmented database. One table |> has over 170 extents, and many have over 8 extents. The database |> is in use 6 days a week, 24 hours. I need to justify taking the |> database down for a few days to fix things. A couple of questions. |> |> Does anyone know what happens when you go over the max number of |> extents for a table? Does everything stop working or just |> adding rows to one table? I would really like hear from anyone |> who has gone over the max number of extents. It depends on which version of OnLine you are using. For 5.X and earlier, the active tblspace structure in shared memory (the one you tune with TBLSPACES, or for you real old-timers, PARTITIONS, in your onconfig file) contained an array of 8 extent structures, which are used to track the extent info for the tblspace. When you went past 9 extents, it would have to go to the tblspace tblspace page on disk to find the location of the additional extents. If the table was not frequently accessed, that page would not likely have been cached, so a page i/o would be necessary. For a frequently accessed table, the TT page was likely to have been cached, so the overhead would be minimal. Still, the first 9 extents were considered "fast access" extents, and the manual recommended keeping the number of extents to 8 or fewer (don't know how 9 became 8). The TT is another matter. Pre 6.0, only 1 page per tblspace was allowed, so the number of extents was limited to the number of extent structures (8 bytes) that could fit on one page after all the other structures were taken into account. This varied, but typically wound up somewhere around 200 (the more indexes, the fewer extents, since index info is tracked in the TT as well). Once you hit your max, you could not allocate any more extents. If the rest of them were full, you would not be able to add any rows to your table. You would get error -131 ISAM error: no free disk space. As of release 6.0, the TBLSPACE structure has changed, so where there was an array of extent structures there is now a pointer to an extent structure. Space is simply allocated to accomodate all the extents for the tblspace. So now ALL the extents can be considered "fast access". The TT structure is different, too, allowing > one page per tblspace, so you should not run out of extents due to the page limitation, but the number of extents is tracked by a 2 byte integer, so you are limited to 32K extents, though I wouldn't recommend having this many ;-). That all said, there is still something to be said about having your data in fewer (ideally one) extent(s). That is, it means all the table data is stored contiguously on disk, making sequential scans, at least, a bit quicker (since it won't have to jump around on disk). Is this worth spending a lot of time reorganizing your data. Maybe. Depends on your application and how the data is commonly accessed. |> What kind of performance gain should we expect from fixing |> the extents? 10%, 20%, etc ...?? I'd be surprised if you got as much as 10%, but your mileage may very well vary. Dave Kosenko Disclaimer: All opinions expressed in this message are well-reasoned and insightful; needless to say, they are not those of Informix Software, its partners or lackeys. Anyone who says otherwise is itching for a fight. **************************************************************************** "I look back with some satisfaction on what an idiot I was when I was 25, but when I do that, I'm assuming I'm no longer an idiot." - Andy Rooney