RE: Maximum Extent size

Potentially prior experience with Oracle would cause concern with # of extents. For example for you Informix people who don't need to be very concerned with # of extents (I know, I'm one of you)..........I have a rather large EMPTY oracle tablespace that in practice runs takes over 100 hours to drop (yes this is an empty tablespace). I am in the process of migrating this database from dictionary managed space to locally managed space. The way Oracle deals with space management on dictionary managed tablespaces is to put it kindly, not efficient. (locally managed tablespaces are better). I would agree ....with Informix, in this case, fragmenting to avoid 32Gb table size limit is more important than controlling # of extents. Norma Jean -----Original Message----- From: informix-list-bounces@iiug.org [mailto:informix-list-bounces@iiug.org] On Behalf Of Keith Simmons Sent: Wednesday, September 13, 2006 5:26 AM To: informix-list@iiug.org Subject: Re: Maximum Extent size In IDS 7.31 the max size is 2Gb for both first and next extent. (This is max chunk size as well). Why do you feel the need to unload and recreate? 60 extents is not that big for the size of table. If you do go this path, have you considered detaching indexes (not if you have UD4 !!) or fragmenting the table? Keith On 13 Sep 2006 03:10:44 -0700, mchivaviro@okzim.co.zw <mchivaviro@okzim.co.zw> wrote: > I am using IDS 7.31 and I have a big table which is now 22gigs in size. > It now has 60 extents and I need to unload and recreate the table > again. What is the maximum extent size I can create and the next size > of the extent. > > _______________________________________________ > Informix-list mailing list > Informix-list@iiug.org > http://www.iiug.org/mailman/listinfo/informix-list > _______________________________________________ Informix-list mailing list Informix-list@iiug.org http://www.iiug.org/mailman/listinfo/informix-list ============================================================ The information contained in this message may be privileged and confidential and protected from disclosure. If the reader of this message is not the intended recipient, or an employee or agent responsible for delivering this message to the intended recipient, you are hereby notified that any reproduction, dissemination or distribution of this communication is strictly prohibited. If you have received this communication in error, please notify us immediately by replying to the message and deleting it from your computer. Thank you. Tellabs ============================================================

Let me add... In a pre-9.4 version (or 9.4+ without large file/chunk support), remember the overhead we take when considering what is the "max extent size." Even in a brand-new dbspace (non-root..(that's funny if you think about)) with a single chunk, we initially take 53 pages off the top (2 pages unused, 1 chunky free list page, and 50 pgs for the tablespace tablespace...53. For a subsequent chunk to an existing dbspace, we take 3 pages off the top...2 unused, 1 chunky free list page. So, under the premise that many are still living with the 2G limitation, make sure you put this stuff in your calculations. For IDS 9.4+ with large chunks turned on, the overhead is the all things equal same, but then it is possible to have 2G contiguous. Although remember that in 10.0+ you can size the tablespace tablespace for root (in the onconfig prior to engine first-time build), or via onspaces for a non-root dbspace. This would change the overhead of 50 pages for the initial/next extents of the Tblspace Tblspace, therefore changing +/- the overhead. Oh - also starting in 9.2 the overhead for the root tblspace tblspace is 250 pages not 50. Not really pertinent to the question, but since I was rambling on about overhead.... ....that was fun...a little internals for your day. HTH - Mark Scranton Xtivia Inc. Informix 1995-2006 Just remember...every December there is a whole new "herd" of "on*" commands available... Sebastian, Norma J. wrote: > Potentially prior experience with Oracle would cause concern with # of > extents. > For example for you Informix people who don't need to be very concerned > with # of extents (I know, I'm one of you)..........I have a rather > large EMPTY oracle tablespace that in practice runs takes over 100 hours > to drop (yes this is an empty tablespace). I am in the process of > migrating this database from dictionary managed space to locally managed > space. The way Oracle deals with space management on dictionary managed > tablespaces is to put it kindly, not efficient. (locally managed > tablespaces are better). > > > I would agree ....with Informix, in this case, fragmenting to avoid 32Gb > table size limit is more important than controlling # of extents. > > Norma Jean > > > > -----Original Message----- > From: informix-list-bounces@iiug.org > [mailto:informix-list-bounces@iiug.org] On Behalf Of Keith Simmons > Sent: Wednesday, September 13, 2006 5:26 AM > To: informix-list@iiug.org > Subject: Re: Maximum Extent size > > In IDS 7.31 the max size is 2Gb for both first and next extent. (This is > max chunk size as well). Why do you feel the need to unload and > recreate? > 60 extents is not that big for the size of table. > If you do go this path, have you considered detaching indexes (not if > you have > UD4 !!) or fragmenting the table? > > Keith > > On 13 Sep 2006 03:10:44 -0700, mchivaviro@okzim.co.zw > <mchivaviro@okzim.co.zw> wrote: > > I am using IDS 7.31 and I have a big table which is now 22gigs in > size. > > It now has 60 extents and I need to unload and recreate the table > > again. What is the maximum extent size I can create and the next size > > of the extent. > > > > _______________________________________________ > > Informix-list mailing list > > Informix-list@iiug.org > > http://www.iiug.org/mailman/listinfo/informix-list > > > _______________________________________________ > Informix-list mailing list > Informix-list@iiug.org > http://www.iiug.org/mailman/listinfo/informix-list > ============================================================ > The information contained in this message may be privileged > and confidential and protected from disclosure. If the reader > of this message is not the intended recipient, or an employee > or agent responsible for delivering this message to the > intended recipient, you are hereby notified that any reproduction, > dissemination or distribution of this communication is strictly > prohibited. If you have received this communication in error, > please notify us immediately by replying to the message and > deleting it from your computer. Thank you. Tellabs > ============================================================