Re-claim un-used space
Question: how to reclaim unused space in table extents/chunks, and why a minimum extent of several contiguous pages is allocated. Answers: the minimum/default extent is 4 pages (8KB in a 2K-page dbspace) and can't be reduced; it's for insert efficiency. To free unused space, reorganize the table via ALTER FRAGMENT ... INIT IN, cluster an index, unload/drop/recreate/reload (UNLOAD/LOAD, HPL, or external tables for speed), or use the REPACK feature in 11.50. It was also confirmed that the table is locked during such a reorganization.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
Hi, How can we re-claim the unused space in Extents/Chunks ? Hence, why the database allocates 6 Contiguous Pages for an Extent ? Does allocation of new pages in an Extent require 6 Contiguous Pages required everytime ? Thanks
If you mean how can I reclaim unused space from my tables, the usual answers are alter an index on your table(s) to cluster or you can use the ALTER FRAGMENT command with the INIT IN option to reorganize tables. --EEM > -----Original Message----- > From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of > SHAHZAD SALAM KASI > Sent: Friday, February 26, 2010 6:36 AM > To: ids@iiug.org > Subject: Re-claim un-used space [19140] > > Hi, > How can we re-claim the unused space in Extents/Chunks ? > > Hence, why the database allocates 6 Contiguous Pages for an Extent ? > Does > allocation of new pages in an Extent require 6 Contiguous Pages > required > everytime ? > > Thanks > > > *********************************************************************** > ******** > Forum Note: Use "Reply" to post a response in the discussion forum.
The minimum extent size is 4 pages or 8KB in a 2K pagesize dbspace and the default extent size if you do not specify a specific size. This is intended to make inserts into the table more efficient while inserting rows into the existing extent pages. You can't reduce this further than four pages. As far as recovering unused pages beyond the current extent size and next size of the table for use by other tables, you have to either reorg the table using ALTER FRAGMENT ... INIT IN ..., unload/drop/create/reload the table, cluster an index, or use the new REPACK feature if you have 11.50. Art Art S. Kagel Advanced DataTools (www.advancedatatools.com) IIUG Board of Directors (art@iiug.org) See you at the 2010 IIUG Informix Conference April 25-28, 2010 Overland Park (Kansas City), KS www.iiug.org/conf Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Advanced DataTools, the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Fri, Feb 26, 2010 at 7:35 AM, SHAHZAD SALAM KASI <skasi@i2cinc.com>wrote: > Hi, > How can we re-claim the unused space in Extents/Chunks ? > > Hence, why the database allocates 6 Contiguous Pages for an Extent ? Does > allocation of new pages in an Extent require 6 Contiguous Pages required > everytime ? > > Thanks > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --000e0cd1ff3eedb2af0480815736
Complementing Art answer about recovering unused space when you choose unload/drop/create/reload, you can use: - UNLOAD/LOAD sql statment (useful only for small tables) - HLP in express or no conversion mode is more fast - Use external tables (>11.50 xC6) , what is similar to HPL/UNLOAD/LOAD, but is faster if you use the 'informix' format (binary)
Thanks for the reply. While the re-organization of table, it gets locked or not? Thanks
The table is locked. Art Art S. Kagel Advanced DataTools (www.advancedatatools.com) IIUG Board of Directors (art@iiug.org) See you at the 2010 IIUG Informix Conference April 25-28, 2010 Overland Park (Kansas City), KS www.iiug.org/conf Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Advanced DataTools, the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Sat, Feb 27, 2010 at 9:46 AM, SHAHZAD SALAM KASI <skasi@i2cinc.com>wrote: > Thanks for the reply. > While the re-organization of table, it gets locked or not? > > Thanks > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --0016364c7ed35a526a04809f59a3