Re: How to reuse pages in other tables?

Leonids.Voroncovs@dati.lv wrote: > Created a table: CREATE TABLE ... > Checked: oncheck -pT database:table > Number of extents 1 > First extent size 4 > Next extent size 4 > Number of pages allocated 8 > Number of pages used 7 > Number of data pages 0 > Number of rows 0 > 1st question: Why number of pages allocated is 8 in 1 extent? You set EXTENT SIZE and NEXT SIZE to 4 pages but Informix will compress contiguous extents into a single extent containing all of the pages from both. That is what has happened here, when the second extent was needed no other extents had yet been allocated in the chunk so the new extent was contiguous with the old one and they were compressed into one 8 page extent. > Loaded data: LOAD FROM ... > Checked: oncheck -pT database:table > Number of extents 2 > First extent size 4 > Next extent size 4 > Number of pages allocated 9336 > Number of pages used 9334 > Number of data pages 7955 > Number of rows 103411 > 2nd question: Why number of pages allocated is 9336 in 2 extents? Same here except that either: 1) At some point another table had allocated an extent from the same chunk so that the next extent allocated to this table was not contiguous with the first extent so that a second was created. 2) There was not enough room in the chunk containing the first extent to allocate a full "NEXT SIZE" extent there and the extent was allocated in another chunk (extents cannot cross chunk boundaries as they must contain contiguous space). 3) Some space was freed up on the chunk (by dropping a table detached index or database) that was physically before the current extent on the chunk and that space was grabbed for the next extent. > Deleted data: DELETE FROM ... > Checked: oncheck -pT database:table > Number of extents 2 > First extent size 4 > Next extent size 4 > Number of pages allocated 9336 > Number of pages used 9334 > Number of data pages 0 > Number of rows 0 > 3rd question: Can I use pages allocated for this table ( but not used ) > for other tables? Only by dropping the table and recreating it. You can perform such compression on a table with data by either 1) unload the data (using dbaccess/4Gl unload command, dbexport, or onunload), drop the table, recreate the table, reload the data or 2) create a new table insert into new_table select * from old_table, then drop old_table, rename new_table to old_table. Art S. Kagel