RE: extents primer - what to wory about and when to worry?

The number of extents in a table can be a significant problem. One of the major design criteria behind the product and the disk architecture was to remove the performance problems caused by operating systems allocating many small units of disk space to the same file. The objective when configuring an Informix database on any release is to have a large contiguous block for a table. If that is the case the databse engine can use many different techniques for searching the table. The more a table gets segmented the less options the system has to make efficient disk access. So, although it is often possible to have large numbers of extents in the same table it is not that advisable. Many of us who have worked with the product almost from day 1 have always worked on the basis that when designing a disk layout we aim for at most a handful of extents in a table. With modern advances in technology the effects of having contiguous space are becoming less and less a concern, however I still subscribe to the view that a well-planned disk structure can be controlled much better than one in which no consideration is given to disk space allocation. I know only too well the problems of trying to rectify disk structures where default extent sizes were used and customers suffered as a result. I always look for tables with large numbers of extent sizes. That often gives me an indication as to how much thought went into disk structure and that often gives me an indication of how well-managed the system is. And what do I mean about the large number of extents? Anything above 15 starts me to look closer. And if there are large numbers of tables with large numbers of extents then I get very nervous. I am constantly coming across systems where no consideration was given to extent sizing. One such system that I am working with has more than 10 tables with more than 150 extents and one table has 198 extents. Now there is a bit of folk-lore that I am probably guilty of starting in the days of Turbo/Online v 4 which says there is a maximum of 200 extents for a table. That is not as fixed a number as it was but is also dependent on page size. I believe that for releases up to 9.3 of IDS, 5.2 of Online, the limits were close to 200 for 2K page sizes and nearer to 350 for 4K page sizes. I did the maths on a few tables and ended up with different maximums. However I haven't done the same for 9.4 yet. And how do we know how many extents in a table, either oncheck -pt database:table or use the sysmaster database and select * from systabinfo, systabnames where partnum =ti_partnum. That will give you an idea and I expect you can take it from there. Regards Malcolm -----Original Message----- From: owner-informix-list@iiug.org [mailto:owner-informix-list@iiug.org] On Behalf Of freakyfreak Sent: 11 October 2004 13:45 To: informix-list@iiug.org Subject: extents primer - what to wory about and when to worry? Is there a good primar on this subject? I see a lot of posts on the subject and I know I have at least one table at 150 extents and climbing. Sorry for the inexperianced question, but the info I have found in the manual is more about how to calc and set extent size more so than it is how to administer/what to watch and worry about type info and I inherited this DB. sending to informix-list