Max index size
A DBA on IDS 11.50.FC5 asked the maximum index key size while building an index for a PeopleSoft table containing several char(254) columns, and whether a functional index could get around it. Answers: the 16-column limit per key still applies (functional indexes in Java/SPL can reference more columns, up to 256, plus 15 ordinary key columns). On the byte limit, an initial answer of 256 bytes was corrected: from version 10 onward max key size depends on dbspace page size, roughly (pagesize-39)/5-1 — about 390 bytes on a 2K page and up to 3257 bytes on a 16K page, so placing the index in a larger-page dbspace is the workaround. No Oracle comparison was given.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
Does anyone know off the top of there head what the maximum size of an index is? I'm using v11.50.fc5 and trying to build an index for a new PeopleSoft table ... Also, can you get around the size limitation by doing a functional index? Thanks for the assistance! Peter Logan Senior Database Administrator Phone: 616/878-8309
This actually happened to me about 10 years ago (I believe it was version 7). At that time, there was a 16-column limit on an index. PeopleSoft had actually built a 16-column index, and we needed to add a new field to the index. Ultimately, we had to decide which of the original 16 fields could/should be dropped from the index. I'm not sure if the 16-column limit still applies in version 11. Jeff Mitchell -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of Peter_Logan@spartanstores.com Sent: Thursday, October 08, 2009 7:47 AM To: ids@iiug.org Subject: Max index size [17421] Does anyone know off the top of there head what the maximum size of an index is? I'm using v11.50.fc5 and trying to build an index for a new PeopleSoft table ... Also, can you get around the size limitation by doing a functional index? Thanks for the assistance! Peter Logan Senior Database Administrator Phone: 616/878-8309 ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.
Actually it's not the number of columns ... it's the size of them ... this index has several char(254) in it ... The way around the 16 column limit is fixed by doing a functional index .... Peter Logan Senior Database Administrator Phone: 616/878-8309 From: "Mitchell, Jeffrey J." <JJMitchell@west.com> To: ids@iiug.org Date: 10/08/2009 09:08 AM Subject: RE: Max index size [17422] Sent by: ids-bounces@iiug.org This actually happened to me about 10 years ago (I believe it was version 7). At that time, there was a 16-column limit on an index. PeopleSoft had actually built a 16-column index, and we needed to add a new field to the index. Ultimately, we had to decide which of the original 16 fields could/should be dropped from the index. I'm not sure if the 16-column limit still applies in version 11. Jeff Mitchell -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of Peter_Logan@spartanstores.com Sent: Thursday, October 08, 2009 7:47 AM To: ids@iiug.org Subject: Max index size [17421] Does anyone know off the top of there head what the maximum size of an index is? I'm using v11.50.fc5 and trying to build an index for a new PeopleSoft table ... Also, can you get around the size limitation by doing a functional index? Thanks for the assistance! Peter Logan Senior Database Administrator Phone: 616/878-8309 ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum. ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.
Maximum key size is 16 columns or 256 bytes. With a functional index written in C the column count is still 16, but Java and SPL based functional indexes can have up to 256 columns in the functional index call plus 15 non-function keys. However, the total length of the resulting indexed key (function return value plus other key columns) still cannot exceed 256 bytes. Art Art S. Kagel Oninit (www.oninit.com) IIUG Board of Directors (art@iiug.org) Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Oninit, the IIUG, nor any other organization with which I am associated either explicitly or implicitly. 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 Thu, Oct 8, 2009 at 8:46 AM, Peter_Logan@spartanstores.com < Peter_Logan@spartanstores.com> wrote: > Does anyone know off the top of there head what the maximum size of an > index is? I'm using v11.50.fc5 and trying to build an index for a new > PeopleSoft table ... Also, can you get around the size limitation by > doing a functional index? Thanks for the assistance! > > Peter Logan > Senior Database Administrator > Phone: 616/878-8309 > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --000e0cdfd704baf1fb04756da9e3
Thanks Art ... is there any way around the 256 limit ... you wouldn't happen to know what the Oracle limit is ? Peter Logan Senior Database Administrator Phone: 616/878-8309 From: "Art Kagel" <art.kagel@gmail.com> To: ids@iiug.org Date: 10/08/2009 10:57 AM Subject: Re: Max index size [17431] Sent by: ids-bounces@iiug.org Maximum key size is 16 columns or 256 bytes. With a functional index written in C the column count is still 16, but Java and SPL based functional indexes can have up to 256 columns in the functional index call plus 15 non-function keys. However, the total length of the resulting indexed key (function return value plus other key columns) still cannot exceed 256 bytes. Art Art S. Kagel Oninit (www.oninit.com) IIUG Board of Directors (art@iiug.org) Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Oninit, the IIUG, nor any other organization with which I am associated either explicitly or implicitly. 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 Thu, Oct 8, 2009 at 8:46 AM, Peter_Logan@spartanstores.com < Peter_Logan@spartanstores.com> wrote: > Does anyone know off the top of there head what the maximum size of an > index is? I'm using v11.50.fc5 and trying to build an index for a new > PeopleSoft table ... Also, can you get around the size limitation by > doing a functional index? Thanks for the assistance! > > Peter Logan > Senior Database Administrator > Phone: 616/878-8309 > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --000e0cdfd704baf1fb04756da9e3 ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.
Silly question. I try to be as ignorant of Orable features as is practical. ;-) Art Art S. Kagel Oninit (www.oninit.com) IIUG Board of Directors (art@iiug.org) Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Oninit, the IIUG, nor any other organization with which I am associated either explicitly or implicitly. 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 Thu, Oct 8, 2009 at 11:02 AM, Peter_Logan@spartanstores.com < Peter_Logan@spartanstores.com> wrote: > Thanks Art ... is there any way around the 256 limit ... you wouldn't > happen to know what the Oracle limit is ? > > Peter Logan > Senior Database Administrator > Phone: 616/878-8309 > > From: > "Art Kagel" <art.kagel@gmail.com> > To: > ids@iiug.org > Date: > 10/08/2009 10:57 AM > Subject: > Re: Max index size [17431] > Sent by: > ids-bounces@iiug.org > > Maximum key size is 16 columns or 256 bytes. With a functional index > written in C the column count is still 16, but Java and SPL based > functional > indexes can have up to 256 columns in the functional index call plus 15 > non-function keys. However, the total length of the resulting indexed key > (function return value plus other key columns) still cannot exceed 256 > bytes. > > Art > > Art S. Kagel > Oninit (www.oninit.com) > IIUG Board of Directors (art@iiug.org) > > Disclaimer: Please keep in mind that my own opinions are my own opinions > and > do not reflect on my employer, Oninit, the IIUG, nor any other > organization > with which I am associated either explicitly or implicitly. 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 Thu, Oct 8, 2009 at 8:46 AM, Peter_Logan@spartanstores.com < > Peter_Logan@spartanstores.com> wrote: > > > Does anyone know off the top of there head what the maximum size of an > > index is? I'm using v11.50.fc5 and trying to build an index for a new > > PeopleSoft table ... Also, can you get around the size limitation by > > doing a functional index? Thanks for the assistance! > > > > Peter Logan > > Senior Database Administrator > > Phone: 616/878-8309 > > > > > > > > > > > ******************************************************************************* > > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > --000e0cdfd704baf1fb04756da9e3 > > > > ******************************************************************************* > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --0015174ff09460ec2f04756dd615
If you are talking a version of 10 or higher the maximum key size was increased. While the number of columns can only be 16 the maximum keysize is a function of your pagsize. 2048 IDS pagesize max key size 390 bytes 16384 IDS pagesize max key size 3257 bytes The formula is (IDS pagesize - 39)/5 -1 =3D max keysize So the largest index key can be placed on a 16K pagesize which is 3257 bytes John F. Miller III STSM, Support Architect miller3@us.ibm.com 503-578-5645 IBM Informix Dynamic Server (IDS) = From: "Art Kagel" <art.kagel@gmail.com> = = To: ids@iiug.org = = Date: 10/08/2009 07:57 AM = = Subject: Re: Max index size [17431] = = Sent by: ids-bounces@iiug.org = = Maximum key size is 16 columns or 256 bytes. With a functional index written in C the column count is still 16, but Java and SPL based functional indexes can have up to 256 columns in the functional index call plus 15= non-function keys. However, the total length of the resulting indexed k= ey (function return value plus other key columns) still cannot exceed 256 bytes. Art Art S. Kagel Oninit (www.oninit.com) IIUG Board of Directors (art@iiug.org) Disclaimer: Please keep in mind that my own opinions are my own opinion= s and do not reflect on my employer, Oninit, the IIUG, nor any other organiza= tion with which I am associated either explicitly or implicitly. 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 Thu, Oct 8, 2009 at 8:46 AM, Peter_Logan@spartanstores.com < Peter_Logan@spartanstores.com> wrote: > Does anyone know off the top of there head what the maximum size of a= n > index is? I'm using v11.50.fc5 and trying to build an index for a new= > PeopleSoft table ... Also, can you get around the size limitation by > doing a functional index? Thanks for the assistance! > > Peter Logan > Senior Database Administrator > Phone: 616/878-8309 > > > > ***********************************************************************= ******** > Forum Note: Use "Reply" to post a response in the discussion forum. > > --000e0cdfd704baf1fb04756da9e3 ***********************************************************************= ******** Forum Note: Use "Reply" to post a response in the discussion forum. =
> On Thu, Oct 8, 2009 at 8:46 AM, Peter_Logan@spartanstores.com wrote: > > > Does anyone know off the top of there head what the maximum size of an > > index is? I'm using v11.50.fc5 and trying to build an index for a new > > PeopleSoft table ... Also, can you get around the size limitation by > > doing a functional index? Thanks for the assistance! > On Thu, Oct 8, 2009 at 07:56, Art Kagel <art.kagel@gmail.com> wrote: > Maximum key size is 16 columns or 256 bytes. Maximum key size is 16 columns - correct. However, on a 2 KB page, the maximum key size is 390 bytes. On bigger page sizes, the maximum key size increases; IIRC, the maximum key size is approximately page size / 5 (meaning you must be able to fit 5 keys on an index page). So, the maximum index key size depends on the page size of the dbspace in which you place it. > With a functional index > written in C the column count is still 16, but Java and SPL based > functional > indexes can have up to 256 columns in the functional index call plus 15 > non-function keys. However, the total length of the resulting indexed key > (function return value plus other key columns) still cannot exceed 256 > bytes. > I believe the limit in terms of bytes is likely to be 390 or more, as before. -- Jonathan Leffler #include <disclaimer.h> Email: jleffler@earthlink.net, jleffler@us.ibm.com Guardian of DBD::Informix v2008.0513 -- http://dbi.perl.org/ "Blessed are we who can laugh at ourselves, for we shall never cease to be amused." NB: Please do not use this email for correspondence. I don't necessarily read it every week, even. Pablo Picasso<http://www.brainyquote.com/quotes/authors/p/pablo_picasso.html>; - "Computers are useless. They can only give you answers." --000e0cd2e2fe586f980475da6b97