Re: Large table extents

costrom@solair1.inter.NL.net (Craig Ostrom) wrote: >Can anyone answer the following questions? >1. When rows are deleted from a table, are the remaining rows left > scattered over the table extent? >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 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. >Is there anything else that can be done to stop this kind of fragmentation? >Thanks for your time. >Stanton Attree. Informix does leave the holes but will use them on an insert if a new row belongs there. You can create a cluster index or alter an index to cluster to regroup the table. One thing to keep in mind when you alter index to cluster informix actually rewrites the table and needs enough space to have two copies of the table for a short period of time. If you do not run update statistics often the optimize will be confused as to the actual layout of the table and may choose the wrong path. -- Tolly Brumbelow, Informix DBA email:tolly.brumbelow@bridge.bellsouth.com