Re: Large table extents

>From: costrom@solair1.inter.NL.net (Craig Ostrom) >Date: Wed, 13 Mar 1996 10:57:33 GMT >X-Informix-List-Id: <news.22054> > >Can anyone answer the following questions? > >1. When rows are deleted from a table, are the remaining rows left > scattered over the table extent? They are left whereever they were before the delete, so the answer is yes. Just doing a delete, even a bulk delete, does not reorganize the other rows. All else apart, it would violate the requirements for ROWIDs. >2. When these remaining rows are queried, given that 1. is true, does > Informix sequentially scan through the whole table extent including > empty pages? I'm guessing, but I think it is a plausible guess. Even if the query requires a sequential scan, the engine would not scan completely empty pages because they are marked as empty in the tablespace bitmap and the engine would know that there is no need to read the empty pages, so it wouldn't do so. >I ask this because that is what seems to be happening and if it is, then >we need a way to defragment the table extent. > >I would think that this means unloading and re-loading the table. Or doing an ALTER TABLE, or, more plausibly, an ALTER INDEX TO CLUSTER. These are simpler, and should be quicker. >Is there anything else that can be done to stop this kind of fragmentation? Not deleting large quantities of data? :-) Yours, Jonathan Leffler (johnl@informix.com) #include <disclaimer.h>