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. Not true. The rows won't get compressed, but the page will. Whenever an insert is done into a page in which there is room for a new row, but insufficient room between the last slot occupied and the beginning of the slot table. > > >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. The point I was trying to get at is "How does OnLine do a sequential scan?". Does it only read data pages? If so, how does it use Big Buffers as data pages are interspersed with index pages? When does it stop? (ie how does it know it has read the last data page?) Does it look in the bitmap page, or does it scan the whole extent? (I assume/ hope the former). A problem might arise because, when the last row is deleted from a page, the bitmap page is updated to show that it is no longer a data page. The page header is not updated however, and still indicates that it is a data page. If OnLine reads eight pages into a Big Buffer, as we are led to believe, does it then scan the headers of those pages looking for data pages, or does it rely on the bitmap page? If the next eight pages contain seven non-home pages and one home data page (unlikely, I'll grant you, unless we're talking about the first eight pages) does it still use a Big Buffer? What if the next eight pages are *all* non-home pages??? Another scenario that intrigues me is when the next eight pages contain, let's say, three index pages and five data pages, all five of which are already in the buffer cache. What will OnLine read next? And what if only four of the five were already in the cache? How is buffer contention avoided on pages that are in a Big Buffer? Are they part of the buffer hash table, or must pages be individually transferred to the main buffer cache? Cheers, graeme --------------------------------------------------------------------------- -m------- Graeme Sargent Voice: +44 (0)252 373035 ---mmm----- Senior Database Consultant Fax : +44 (0)252 373135 -----mmmmm--- Pyramid Technology Ltd. Telex: Tell who??? -------mmmmmmm- Farnborough, Hants GU14 7PL Email: graeme@pyra.co.uk --------------------------------------------------------------------------- We have the technology. The tricky bit is learning how to use it. Cheers, graeme --------------------------------------------------------------------------- -m------- Graeme Sargent Voice: +44 (0)252 373035 ---mmm----- Senior Database Consultant Fax : +44 (0)252 373135 -----mmmmm--- Pyramid Technology Ltd. Telex: Tell who??? -------mmmmmmm- Farnborough, Hants GU14 7PL Email: graeme@pyra.co.uk --------------------------------------------------------------------------- We have the technology. The tricky bit is learning how to use it.