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

Lester, a short-term expedient would be to modify the table with 170 extents to make the NEXT SIZE a much larger value. How long has it taken to fill 170 extents? If it is only a few months then you probably need to do a lot of work on extent sizing. If it has been a few years then this might always happen with sone of them. Form my limited experience, when you hit the maximum number of extents (which is a number approximating to 200 on some hardware platforms! but may not be on others,) the system returns "out of Disk Space" error. You can't add anything to the table and must UNLOAD and LOAD with different extent sizes. I would expect you to get some performance gain by reducing the number of extents. How much? I would need to know a lot more about how critical this table was to your application and the frequency of access. I would also warn that you may not get any improvement, and could possibly get degradation. Unloading and loading affects the juxtaposition of indexes and data and could have detrimental effects just as much as advantages. I'm not saying DON'T unload/load but look at the application and study the current index/data layout using TBCHECK(ONCHECK). Then think about the relationships between the access to this table and to other tables. Finally, everything you've got two of keep crossed. And good luck. > Hello > > 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. > > What kind of performance gain should we expect from fixing > the extents? 10%, 20%, etc ...?? > > Thanks for sharing your experience. > > Regards - Lester > > > ######################################################################## > ###### Lester Knutsen > lester@access.digex.net ## Advanced DataTools Corporation > Voice: 703-256-0267 ## Grant group privileges for Informix > databases with DB Privileges ## Visit our Web page: > http://www.access.digex.net/~lester > ######################################################################## > ###### > Malcolm Weallans Online Database Consultancy Phone 0628-72154 Fax 0628-37463 CIX - onlinedbc