Re: oncheck -pT question

mukta mohindra wrote: > > I altered a table columns from char(60) to varchar(100). > When I did oncheck -pT on the table the extents were collapsed into 1 > extent, however the first and next extent size still showed 8 when the > total number of pages allocated was 160. Why is that ? > > Also, the amount of free space in Home Page increased 4 times. Why is > that? I had expected all the data to be rearranged and packed with some > percentage as free space on each page. > > Also, when you do an alter table does informix assign a new extent and > marks the existing ones as free? When you ALTER TABLE and change the table schema a new table with the new schema is created in it's own extent(s) and the data from the original table is copied in the old table dropped and the new one renamed. The single large extent as I explained to another post yesterday is due to extent compression. The engine compresses contiguous extents into a single extent to reduce search overhead. The increase in free space on home pages is probably just due to the new rowsize -vs- the old row size. When the ALTER TABLE is creating the new table it completely fills each page (as far as complete rows fit on a page anyway) the free space on home pages at that point is all slack or unused space on home pages that is not large enough to hold any data row. In general the size of slack is: 2020 MOD (rowsize + 8) -- 8 bytes for the row's slot entry. For variable length records (rows with varchar) this is more complicated since individual rows may be a few to many bytes larger than the minimum rowsize. One might guess it is: (2020 MOD (minsize + 8)) < slack < (2020 MOD (maxsize + 8)) but this is omplicated further by the extra slot entries for rows that have grown and had to be moved to another page and the fact that certain row sizes between minsize and maxsize might leave no slack if all the rows on the page were that size. So all bets on estimating slack for varchar are off. Art S. Kagel