Maximum size of index pages per fragment
User asked about the maximum size of index pages per fragment in Informix. Multiple experts confirmed it is the same as data pages: 16,775,134 pages per fragment. One expert noted internal code may limit index fragments to a 24-bit boundary. Recommendation: use wider pages (16K) or fragment further to avoid approaching the limit.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
Hi, I know that the limit for data pages per fragment is 16,775,134, according to the IBM Informix Administrator's Reference. But I have an index that is fragmented in its own dbspace and currently has 14614945 pages. My fear is that it reaches a limit. My question is, what is the maximum size of index pages per fragment? Thanks in advance, Roger
Roger Exactly the same. Keith On 20 Mar 2013 19:31, "ROGER VILCA" <rvilca@luzdelsur.com.pe> wrote: > Hi, > I know that the limit for data pages per fragment is 16,775,134, according > to > the IBM Informix Administrator's Reference. > But I have an index that is fragmented in its own dbspace and currently has > 14614945 pages. > My fear is that it reaches a limit. > My question is, what is the maximum size of index pages per fragment? > > Thanks in advance, > Roger > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --bcaec501c172803e2d04d8607a67
Wellll. Someone posted about a month ago that there isn't supposed to be any, but looking into the code the individual (an IBMer) saw code that would prevent pages past the 24bit limit in an index fragment from being accessed, so I would either move the index to dbspace(s) with wider pages (16K pages work best for indexes anyway) or fragment it further. Art Art S. Kagel Advanced DataTools (www.advancedatatools.com) Blog: http://informix-myview.blogspot.com/ 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 Wed, Mar 20, 2013 at 3:30 PM, ROGER VILCA <rvilca@luzdelsur.com.pe>wrote: > Hi, > I know that the limit for data pages per fragment is 16,775,134, according > to > the IBM Informix Administrator's Reference. > But I have an index that is fragmented in its own dbspace and currently has > 14614945 pages. > My fear is that it reaches a limit. > My question is, what is the maximum size of index pages per fragment? > > Thanks in advance, > Roger > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --f46d04016b49d937c304d8607b56
You are limited to 16M per dbspace the same as the data pages. Cheers Paul > Wellll. Someone posted about a month ago that there isn't supposed to be > any, but looking into the code the individual (an IBMer) saw code that > would prevent pages past the 24bit limit in an index fragment from being > accessed, so I would either move the index to dbspace(s) with wider pages > (16K pages work best for indexes anyway) or fragment it further. > > Art > > Art S. Kagel > Advanced DataTools (www.advancedatatools.com) > Blog: http://informix-myview.blogspot.com/ > > 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 Wed, Mar 20, 2013 at 3:30 PM, ROGER VILCA > <rvilca@luzdelsur.com.pe>wrote: > >> Hi, >> I know that the limit for data pages per fragment is 16,775,134, >> according >> to >> the IBM Informix Administrator's Reference. >> But I have an index that is fragmented in its own dbspace and currently >> has >> 14614945 pages. >> My fear is that it reaches a limit. >> My question is, what is the maximum size of index pages per fragment? >> >> Thanks in advance, >> Roger >> >> >> >> > ******************************************************************************* >> Forum Note: Use "Reply" to post a response in the discussion forum. >> >> > > --f46d04016b49d937c304d8607b56 > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > -- Paul Watson Tel: +1 913-674-0360 Mob: +1 913-387-7529 Web: www.oninit.com www.advancedatatools.com Failure is not as frightening as regret. If you want to improve, be content to be thought foolish and stupid. What this country needs are more unemployed politicians
On that note. I recently had a situation where I reached the page limit on = a fragment on a table in informix 11.7 fc5 running on aix. It has a poor fr= agmentation stategy but I cannot get the down time at the moment to fix it.= I could not get exclusive access to the table and since the db is unlogge= d could not force the alter fragment statement. To get around the issue at = the time=2C I compressed the fragment. The question that i have is this. I = would like to repack the table. Will i have issues with inserts to the tabl= e while doing this as the table cannot allocate any more extents in the mos= t active fragment? I have tried to emulate this on another instance=2C and a load ... insert a= ppears to work -----Original Message----- From: Keith Simmons Sent: 20 Mar 2013 19:47:00 GMT To: ids@iiug.org Subject: Re: Maximum size of index pages per fragment [29817] Roger Exactly the same. Keith On 20 Mar 2013 19:31=2C "ROGER VILCA" <rvilca@luzdelsur.com.pe> wrote: > Hi=2C > I know that the limit for data pages per fragment is 16=2C775=2C134=2C ac= cording > to > the IBM Informix Administrator's Reference. > But I have an index that is fragmented in its own dbspace and currently h= as > 14614945 pages. > My fear is that it reaches a limit. > My question is=2C what is the maximum size of index pages per fragment? > > Thanks in advance=2C > Roger > > > > ***************************************************************************= **** > Forum Note: Use "Reply" to post a response in the discussion forum. > > --bcaec501c172803e2d04d8607a67 ***************************************************************************= **** Forum Note: Use "Reply" to post a response in the discussion forum.
The REPACK operation moves data a few pages at a time using only transient page level and index key locks so as long as you other applications are running with LOCK MODE WAIT some reasonable few seconds (5-10 is usually adequate) no one is likely to notice more than a slight slowdown in larger queries. INSERT operations should be unaffected. Art Art S. Kagel Advanced DataTools (www.advancedatatools.com) Blog: http://informix-myview.blogspot.com/ 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 Wed, Mar 20, 2013 at 4:27 PM, Mark Tyrer <m_tyrer@hotmail.com> wrote: > On that note. I recently had a situation where I reached the page limit on > = > a fragment on a table in informix 11.7 fc5 running on aix. It has a poor > fr= > agmentation stategy but I cannot get the down time at the moment to fix > it.= > I could not get exclusive access to the table and since the db is unlogge= > d could not force the alter fragment statement. To get around the issue at > = > the time=2C I compressed the fragment. The question that i have is this. I > = > would like to repack the table. Will i have issues with inserts to the > tabl= > e while doing this as the table cannot allocate any more extents in the > mos= > t active fragment? > > I have tried to emulate this on another instance=2C and a load ... insert > a= > ppears to work > > -----Original Message----- > > From: Keith Simmons > Sent: 20 Mar 2013 19:47:00 GMT > To: ids@iiug.org > Subject: Re: Maximum size of index pages per fragment [29817] > > Roger > Exactly the same. > Keith > On 20 Mar 2013 19:31=2C "ROGER VILCA" <rvilca@luzdelsur.com.pe> wrote: > > > Hi=2C > > I know that the limit for data pages per fragment is 16=2C775=2C134=2C > ac= > cording > > to > > the IBM Informix Administrator's Reference. > > But I have an index that is fragmented in its own dbspace and currently > h= > as > > 14614945 pages. > > My fear is that it reaches a limit. > > My question is=2C what is the maximum size of index pages per fragment? > > > > Thanks in advance=2C > > Roger > > > > > > > > > > ***************************************************************************= > **** > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > --bcaec501c172803e2d04d8607a67 > > > ***************************************************************************= > **** > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --485b390f7b2000933f04d862d23a