Re: number of extents

"Denmark B. Weatherburn" wrote: > > Hi Listers, > > I searched my emails for those with topics related to the number of extents. > I found several relating to the maximum number of extents; however, I have > not found any relating to the optimum number of extents for any table. > The Informix manuals don't have recommendations. Perhaps, they resist making > statements that might be proved incorrect or unreliable. They probably don't give a value because it depends. It depends on the way you access a table. If you only ever read one record at a time then it doesn't matter so much that the table is scattered across the disk. The actual answer of course is one. If you can stick to that then you can't go wrong. ;-) > So, I guess it's up to the experts who have done hands-on testing and > analysis to make those kind of statements. Indeed, and you are the only one with your data and application. > I recall hearing or reading that a table should not have more than 8 extents > without being reorganized. Is there any validity in this statement. If not, > please > explain what should be the criteria for determining the optimum number of > extents for a table. Not since version 6. That was for performance in version 5 and older. The one criteria already mentioned is the maximum number of extents. If you exceed that then you have problems, and I have huge alarm bells ringing if any table approaches 200. The general problem with multiple extents is performance, so keep the number as low as possible. > We are using IDS 7.30.UC3 on Solaris 2.7. We are not using fragmented > tables. We are using raw devices. We are not mirroring any disks. > All IDS chunks and dbspaces are on one disk for each SERVER. If you are not IO bound on a single disk, then you probably don't have too many problems. If you are, then I think you need to look at spreading the data across multiple disks. > Is dbexport/dbimport the most effective way to reorganize the data for all > tables into one extent given our configuration? dbexport/dbimport are probably the easiest to use for this purpose. Although the other methods already mentioned such as ALTER FRAGMENT can get you down to 2 extents (or multiples of your chunk size plus 1) if you set the next size large enough. You can always reset it after the move. Cheers, -- Mark. +----------------------------------------------------------+-----------+ | Mark D. Stock mailto:mdstock@mydas.freeserve.co.uk |//////// /| | http://www.informix.com http://www.informixhandbook.com |///// / //| | http://www.iiug.org +-----------------------------------+//// / ///| | |This email will self-destruct in |/// / ////| | |10 sec. If you received this email |// / /////| | |in error, sorry about the mess. |/ ////////| +----------------------+-----------------------------------+-----------+