Re: Question on page compression

}From: graeme@pyra.co.uk (Graeme Sargent) }Subject: Re: Question on page compression }Date: Tue, 19 Oct 1993 23:34:15 GMT }X-Informix-List-Id: <news.4661> } }bi41@rexago8.uucp (Peter Francescani) writes: }>Having supported applications which accessed Standard Engine databases }>I've seen where tables where unloaded, dropped, re-created and re-loaded }>in order to close holes left by end-of-period routines which deleted }>many rows. } }>Is there any reason to continue doing this drop/re-create in an }>_OnLine_ environment, where a table's initial extent is large enough }>to hold all the rows the table may contain? Will page compression }>re-organize the data rows? } }Page compression only re-organizes rows within a page, not within an }extent. And only occurs for tables with VARCHAR fields -- fixed size rows never need compressing. }In SE, there is a benefit from re-organising a table as described, as it }decreases the amount of work involved in doing a sequential scan, and }returns disk space to the operating system. } }In OnLine, there may be a benefit to be gained, but it is less likely. This is correct. The main benefit of rebuilding a table like this is that it can be made to do a ALTER INDEX TO CLUSTER operation so that the data physical order is the same as the order of some index, typically the primary key index. If your extent sizes aren't set sensibly, it can also reduce the number of extents in use. }In your scenario of a single extent table, no disk space would be freed. }I am not convinced that there is likely to be a significant reduction in }the number of data pages, either. And even if there were, I am not }convinced that this makes a great deal of difference to performance, as }the use of the Big Buffers would imply that many non-data pages would be }read in any case. I'm not sure whether the big buffers would make a significant difference, but if a table has a single extent the size of its first extent (ie the extent hasn't grown) then no space will be released. }I'd be interested to know what davek and johnl think on this one. OK, I'm a sucker. I responded. And now you know. Basically, Graeme is correct, in my view. Yours, Jonathan Leffler (johnl@informix.com) #include <disclaimer.h>