Re: How to reuse pages in other tables?

In article <34E08969.7442@bloomberg.com>, Art S. Kagel <kagel@bloomberg.com> writes >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. > Or do alter index to cluster for an index on the table. >Art S. Kagel -- David Williams Maintainer of the Informix FAQ Primary site (Beta Version) http://www.smooth1.demon.co.uk Official site http://www.iiug.org/techinfo/faq/faq_top.html I see you standin', Standin' on your own, It's such a lonely place for you, For you to be If you need a shoulder, Or if you need a friend, I'll be here standing, Until the bitter end... So don't chastise me Or think I, I mean you harm... All I ever wanted Was for you To know that I care