Re: Do extents impact speed?

mweallans@panacea.co.uk wrote: > I am managing a number of systems that were built before I was > involved. Many of these have a large number of extents. There are many > tables with more than 150 extents. As an old hand at Turbo, Online, > and IDS, my gut reaction was that we would suffer performance problems > with large tables like this. But am I right? And how can I prove it? > > Your ideas, as always, would be much appreciated. Everyone made good points and well presented. I just want to add a few: - The most important thing to consider to decide if defragging tables will improve performance is the locality of the data accessed. If all of your queries access only the latest few weeks data and data is never deleted then most queries will only actually touch one or two extents of the table and reorging will not buy you anything in the performance arena (running out of extents of course is a different issue altogether). - If you cannot use dbexport/dbimport try: ALTER FRAGMENT ON <tablename> INIT IN <dbspace|fragmentation expresssion>; 'dbspace' can be the same dbspace the table already resides in or a different one. The ALTER FRAGMENT operation does not change the table's tabid as dbexport/dbimport would so your third party app will not even know that it's happened. You just need the temp space for key sorting to update the index nodes and logical log space for the page write and free log entries. If you lock the table you need not worry about running out of locks in older releases. Art S. Kagel sending to informix-list