Number of bytes stored in an index entry

Hi there, I vaguely remember a conversion with an Informix sage years ago about the storage of keys in IDS's btree. AFAIR he said keys are truncated to about 160 bytes before insertion into the index. I think the reasoning being that that is selective enough and it will save on space. I suppose if this is the case, then by implication index-only queries in these cases must become table-scans. Does this ring any bells with anyone? Is it something that used to be done that now isn't? Regards, Donny P.S. In case you're interested, I was having a conversion with a colleague about how the various RDBMS vendors dealt with long keys and I postulated that IDS did this. I have since been unable to provide any evidence. Doh.

AFAIK, IDS does not truncate any keys in its indexes. B+TREE index keys are limited to 255 bytes and you cannot index any column of combination of columns that, combined, are longer than 255 bytes. If the engine is preventing keys longer than 255 why truncate that key further down to 160 bytes or so as you propose? Also, if keys are truncated, the engine would not be able to perform some of the more advanced index functions that it does like (as you point out) key-only, key-first, and index key filtering on trailing key columns. Nope, IDS indexes the full key. Art On Wed, Mar 11, 2009 at 9:51 AM, DUNCAN RANCE <duncan.rance@coppereye.com>wrote: > Hi there, > > I vaguely remember a conversion with an Informix sage years ago about the > storage of keys in IDS's btree. > > AFAIR he said keys are truncated to about 160 bytes before insertion into > the > index. I think the reasoning being that that is selective enough and it > will > save on space. > > I suppose if this is the case, then by implication index-only queries in > these > cases must become table-scans. > > Does this ring any bells with anyone? Is it something that used to be done > that now isn't? > > Regards, > Donny > > P.S. In case you're interested, I was having a conversion with a colleague > about how the various RDBMS vendors dealt with long keys and I postulated > that > IDS did this. I have since been unable to provide any evidence. Doh. > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > -- 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. --0016364edc328656b60464d8ea46

There are a few little exceptions that you will learn if you attend my tutorial at the IIUG conference "Getting the most out of IDS Indexes". Sorry for the shameless plug. Note 1 ====== With the addition of multiple pages sizes the index key size. has been increased. Here are a few common limits for the maximum key size: You may have at most 16 column/functions per index spread across 3250 bytes on a 16KB pages 1618 bytes on a 8KB page 799 bytes on a 4KB page 390 bytes on a 2KB page Note 2 ===== When using the data types lvarchar or varchars in an index you must account for the maximum when calculating if it will fix in an index, BUT the index only stores the actual data size in the index not the maximum size. Example: If you had an index with a lvarchar(1024) you could use an 8KB page size, but not a 4KB or 2KB pages size. If you insert a the word "JOHN" it would consume about 6 bytes of space in the index (2 bytes for leading length and 4 bytes for data). John F. Miller III STSM, Support Architect miller3@us.ibm.com 503-578-5645 IBM Informix Dynamic Server (IDS) ids-bounces@iiug.org wrote on 03/11/2009 07:45:22 AM: > AFAIK, IDS does not truncate any keys in its indexes. B+TREE index keys are > limited to 255 bytes and you cannot index any column of combination of > columns that, combined, are longer than 255 bytes. If the engine is > preventing keys longer than 255 why truncate that key further down to 160 > bytes or so as you propose? Also, if keys are truncated, the engine would > not be able to perform some of the more advanced index functions that it > does like (as you point out) key-only, key-first, and index key filtering on > trailing key columns. Nope, IDS indexes the full key. > > Art > > On Wed, Mar 11, 2009 at 9:51 AM, DUNCAN RANCE > <duncan.rance@coppereye.com>wrote: > > > Hi there, > > > > I vaguely remember a conversion with an Informix sage years ago about the > > storage of keys in IDS's btree. > > > > AFAIR he said keys are truncated to about 160 bytes before insertion into > > the > > index. I think the reasoning being that that is selective enough and it > > will > > save on space. > > > > I suppose if this is the case, then by implication index-only queries in > > these > > cases must become table-scans. > > > > Does this ring any bells with anyone? Is it something that used to be done > > that now isn't? > > > > Regards, > > Donny > > > > P.S. In case you're interested, I was having a conversion with a colleague > > about how the various RDBMS vendors dealt with long keys and I postulated > > that > > IDS did this. I have since been unable to provide any evidence. Doh. > > > > > > > > > ******************************************************************************* > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > -- > 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. > > --0016364edc328656b60464d8ea46 > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. >