IDS Forum

Re: Number of bytes stored in an index entry

Posted By: John Miller iii
Date: Wednesday, 11 March 2009, at 1:45 p.m.

In Response To: Re: Number of bytes stored in an index entry (Art Kagel)

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.
>

Messages In This Thread

• Number of bytes stored in an index entry
  DUNCAN RANCE -- Wednesday, 11 March 2009, at 9:51 a.m.
  • Re: Number of bytes stored in an index entry
    Art Kagel -- Wednesday, 11 March 2009, at 10:45 a.m.
    • Re: Number of bytes stored in an index entry
      John Miller iii -- Wednesday, 11 March 2009, at 1:45 p.m.

IDS Forum is maintained by Administrator with WebBBS 5.12.