Index on a varchar field
Mary Gregory asked whether indexing VARCHAR columns in IDS 9.40 on HP-UX 11.11 is risky, wanting to widen a char(10) key column (unique and part of composite indexes) across 50M+ rows without wasting space. Replies: one claim that unique indexes on VARCHAR aren't allowed was rebutted by Jonathan Leffler as undocumented and not a real restriction; others noted VARCHARs occupy their full declared length in index pages (so no index space saving) and that tables with VARCHARs can't use light scans, suggesting a sensibly sized CHAR may perform better. John Miller clarified that on the data pages only the actual (or declared minimum) length is stored. No single definitive decision is recorded.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
Hi everyone -- Are there any known issues or gotchas with indexes on varchar fields, or any recommendations for staying away from them? We have a field that is currently char(10), currently unique in itself but part of some composite indexes, too. We need to resize this column to at least char(20) (maybe more) but since we are not 100% sure of what the max size might need to be, I would like to use a varchar data type instead to allow us a larger size if we needed in the future as well as to not waste a lot of disk space (because we are talking probably 50+ million rows among the various tables.) Any thoughts are appreciated!
I do not believe you can create a unique index on a varchar column. If it is part of a composite index, I'm not sure, but most likely not. Try to make an intelligent estimation for a maximum char() size and live with it. IDS version? OS? Bob Roussey Unix / Informix Administration Spirit Airlines Robert.Roussey@SpiritAir.com -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of Mary Gregory Sent: Wednesday, January 03, 2007 3:21 PM To: ids@iiug.org Subject: Index on a varchar field [8083] Hi everyone -- Are there any known issues or gotchas with indexes on varchar fields, or any recommendations for staying away from them? We have a field that is currently char(10), currently unique in itself but part of some composite indexes, too. We need to resize this column to at least char(20) (maybe more) but since we are not 100% sure of what the max size might need to be, I would like to use a varchar data type instead to allow us a larger size if we needed in the future as well as to not waste a lot of disk space (because we are talking probably 50+ million rows among the various tables.) Any thoughts are appreciated! ************************************************************************ ******* Forum Note: Use "Reply" to post a response in the discussion forum.
IDS 9.40, HPUX 11.11 I have read that you can use varchar's in an index, I just want to make sure it's not a bad idea. I can go with a fixed char length, I will just be wasting a lot of space for nothing ... and if that's the best thing to do, that's fine, I was just looking for another alternative. -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of Robert Roussey(MIS) Sent: Wednesday, January 03, 2007 3:33 PM To: ids@iiug.org Subject: RE: Index on a varchar field [8085] I do not believe you can create a unique index on a varchar column. If it is part of a composite index, I'm not sure, but most likely not. Try to make an intelligent estimation for a maximum char() size and live with it. IDS version? OS? Bob Roussey Unix / Informix Administration Spirit Airlines Robert.Roussey@SpiritAir.com -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of Mary Gregory Sent: Wednesday, January 03, 2007 3:21 PM To: ids@iiug.org Subject: Index on a varchar field [8083] Hi everyone -- Are there any known issues or gotchas with indexes on varchar fields, or any recommendations for staying away from them? We have a field that is currently char(10), currently unique in itself but part of some composite indexes, too. We need to resize this column to at least char(20) (maybe more) but since we are not 100% sure of what the max size might need to be, I would like to use a varchar data type instead to allow us a larger size if we needed in the future as well as to not waste a lot of disk space (because we are talking probably 50+ million rows among the various tables.) Any thoughts are appreciated! ************************************************************************ ******* Forum Note: Use "Reply" to post a response in the discussion forum. ************************************************************************ ******* Forum Note: Use "Reply" to post a response in the discussion forum.
Also keep in mind that tables with varchar fields cannot use light scans. Particularly for large data sets (and you indicated this involves millions of rows) this can be a consideration, depending on the type of usage. It may be worth wasting the space because of the performance improvement on table scans. DC > -----Original Message----- > From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of Mary > Gregory > Sent: Wednesday, January 03, 2007 2:38 PM > To: ids@iiug.org > Subject: RE: Index on a varchar field [8086] > > > IDS 9.40, HPUX 11.11 > > I have read that you can use varchar's in an index, I just want to make > sure it's not a bad idea. I can go with a fixed char length, I will > just be wasting a lot of space for nothing ... and if that's the best > thing to do, that's fine, I was just looking for another alternative. > > -----Original Message----- > From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of > Robert Roussey(MIS) > Sent: Wednesday, January 03, 2007 3:33 PM > To: ids@iiug.org > Subject: RE: Index on a varchar field [8085] > > I do not believe you can create a unique index on a varchar column. > If it is part of a composite index, I'm not sure, but most likely not. > Try to make an intelligent estimation for a maximum char() size and live > > with it. > > IDS version? OS? > > Bob Roussey > Unix / Informix Administration > Spirit Airlines > Robert.Roussey@SpiritAir.com > > -----Original Message----- > From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of > Mary Gregory > Sent: Wednesday, January 03, 2007 3:21 PM > To: ids@iiug.org > Subject: Index on a varchar field [8083] > > Hi everyone -- Are there any known issues or gotchas with indexes on > varchar fields, or any recommendations for staying away from them? We > have a field that is currently char(10), currently unique in itself but > part of some composite indexes, too. We need to resize this column to > at least char(20) (maybe more) but since we are not 100% sure of what > the max size might need to be, I would like to use a varchar data type > instead to allow us a larger size if we needed in the future as well as > to not waste a lot of disk space (because we are talking probably 50+ > million rows among the various tables.) > > Any thoughts are appreciated! > > ************************************************************************ > > ******* > Forum Note: Use "Reply" to post a response in the discussion forum. > > ************************************************************************ > ******* > Forum Note: Use "Reply" to post a response in the discussion forum. > > > ************************************************************************ ** > ***** > Forum Note: Use "Reply" to post a response in the discussion forum.
I seem to remember that a varchar in an index allocates as much space as the maximum number of chars you defined, Ie. a varchar(255) will be stored in the "index page" as a char 255. So there is not real space saving at index level. Hospitably yours, Walter Milan DBA -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of Mary Gregory Sent: Wednesday, January 03, 2007 3:38 PM To: ids@iiug.org Subject: RE: Index on a varchar field [8086] IDS 9.40, HPUX 11.11 I have read that you can use varchar's in an index, I just want to make sure it's not a bad idea. I can go with a fixed char length, I will just be wasting a lot of space for nothing ... and if that's the best thing to do, that's fine, I was just looking for another alternative. -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of Robert Roussey(MIS) Sent: Wednesday, January 03, 2007 3:33 PM To: ids@iiug.org Subject: RE: Index on a varchar field [8085] I do not believe you can create a unique index on a varchar column. If it is part of a composite index, I'm not sure, but most likely not. Try to make an intelligent estimation for a maximum char() size and live with it. IDS version? OS? Bob Roussey Unix / Informix Administration Spirit Airlines Robert.Roussey@SpiritAir.com -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of Mary Gregory Sent: Wednesday, January 03, 2007 3:21 PM To: ids@iiug.org Subject: Index on a varchar field [8083] Hi everyone -- Are there any known issues or gotchas with indexes on varchar fields, or any recommendations for staying away from them? We have a field that is currently char(10), currently unique in itself but part of some composite indexes, too. We need to resize this column to at least char(20) (maybe more) but since we are not 100% sure of what the max size might need to be, I would like to use a varchar data type instead to allow us a larger size if we needed in the future as well as to not waste a lot of disk space (because we are talking probably 50+ million rows among the various tables.) Any thoughts are appreciated! ************************************************************************ ******* Forum Note: Use "Reply" to post a response in the discussion forum. ************************************************************************ ******* Forum Note: Use "Reply" to post a response in the discussion forum. ************************************************************************ ******* Forum Note: Use "Reply" to post a response in the discussion forum.
Just to clarify a few things.
1. On disk IDS stores the maximum of the data inserted by the users or=
the
min qualify in the
varchar definition, not the maximum length specified in the schem=
a.
Example
create table t1 ( c1 varchar(255,5) )
insert into t1 values ("J") Store=s 5
byte of data, because of the minimum in the schema file
insert into t1 values ("JOHN MILLER") Store=s 11
bytes of user data
Hope this helps,
John
=
"Walter Milan" =
<Walter_Milan@hil =
ton.com> =
To
Sent by: ids@iiug.org =
ids-bounces@iiug. =
cc
org =
Subj=
ect
RE: Index on a varchar field [80=
88]
01/03/2007 01:47 =
PM =
=
=
Please respond to =
ids@iiug.org =
=
=
I seem to remember that a varchar in an index allocates as much space a=
s
the maximum number of chars you defined, Ie. a varchar(255) will be
stored in the "index page" as a char 255. So there is not real space
saving at index level.
Hospitably yours,
Walter Milan
DBA
-----Original Message-----
From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of
Mary Gregory
Sent: Wednesday, January 03, 2007 3:38 PM
To: ids@iiug.org
Subject: RE: Index on a varchar field [8086]
IDS 9.40, HPUX 11.11
I have read that you can use varchar's in an index, I just want to make=
sure it's not a bad idea. I can go with a fixed char length, I will
just be wasting a lot of space for nothing ... and if that's the best
thing to do, that's fine, I was just looking for another alternative.
-----Original Message-----
From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of
Robert Roussey(MIS)
Sent: Wednesday, January 03, 2007 3:33 PM
To: ids@iiug.org
Subject: RE: Index on a varchar field [8085]
I do not believe you can create a unique index on a varchar column.
If it is part of a composite index, I'm not sure, but most likely not.
Try to make an intelligent estimation for a maximum char() size and liv=
e
with it.
IDS version? OS?
Bob Roussey
Unix / Informix Administration
Spirit Airlines
Robert.Roussey@SpiritAir.com
-----Original Message-----
From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of
Mary Gregory
Sent: Wednesday, January 03, 2007 3:21 PM
To: ids@iiug.org
Subject: Index on a varchar field [8083]
Hi everyone -- Are there any known issues or gotchas with indexes on
varchar fields, or any recommendations for staying away from them? We
have a field that is currently char(10), currently unique in itself but=
part of some composite indexes, too. We need to resize this column to
at least char(20) (maybe more) but since we are not 100% sure of what
the max size might need to be, I would like to use a varchar data type
instead to allow us a larger size if we needed in the future as well as=
to not waste a lot of disk space (because we are talking probably 50+
million rows among the various tables.)
Any thoughts are appreciated!
***********************************************************************=
*
*******
Forum Note: Use "Reply" to post a response in the discussion forum.
***********************************************************************=
*
*******
Forum Note: Use "Reply" to post a response in the discussion forum.
***********************************************************************=
*
*******
Forum Note: Use "Reply" to post a response in the discussion forum.
***********************************************************************=
********
Forum Note: Use "Reply" to post a response in the discussion forum.
=
I agree with Walter, for exemple in a field varchar(100) and you'll insert the value "test" into the data page will store 5 bytes, 4 bytes of the string and 1 to store the size of the string into the index page will store 100 bytes total spaces allocated 105 bytes. Celso Coimbra -----Mensagem original----- De: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org]Em nome de Walter Milan Enviada em: quarta-feira, 3 de janeiro de 2007 19:47 Para: ids@iiug.org Assunto: RE: Index on a varchar field [8088] I seem to remember that a varchar in an index allocates as much space as the maximum number of chars you defined, Ie. a varchar(255) will be stored in the "index page" as a char 255. So there is not real space saving at index level. Hospitably yours, Walter Milan DBA -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of Mary Gregory Sent: Wednesday, January 03, 2007 3:38 PM To: ids@iiug.org Subject: RE: Index on a varchar field [8086] IDS 9.40, HPUX 11.11 I have read that you can use varchar's in an index, I just want to make sure it's not a bad idea. I can go with a fixed char length, I will just be wasting a lot of space for nothing ... and if that's the best thing to do, that's fine, I was just looking for another alternative. -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of Robert Roussey(MIS) Sent: Wednesday, January 03, 2007 3:33 PM To: ids@iiug.org Subject: RE: Index on a varchar field [8085] I do not believe you can create a unique index on a varchar column. If it is part of a composite index, I'm not sure, but most likely not. Try to make an intelligent estimation for a maximum char() size and live with it. IDS version? OS? Bob Roussey Unix / Informix Administration Spirit Airlines Robert.Roussey@SpiritAir.com -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of Mary Gregory Sent: Wednesday, January 03, 2007 3:21 PM To: ids@iiug.org Subject: Index on a varchar field [8083] Hi everyone -- Are there any known issues or gotchas with indexes on varchar fields, or any recommendations for staying away from them? We have a field that is currently char(10), currently unique in itself but part of some composite indexes, too. We need to resize this column to at least char(20) (maybe more) but since we are not 100% sure of what the max size might need to be, I would like to use a varchar data type instead to allow us a larger size if we needed in the future as well as to not waste a lot of disk space (because we are talking probably 50+ million rows among the various tables.) Any thoughts are appreciated! ************************************************************************ ******* Forum Note: Use "Reply" to post a response in the discussion forum. ************************************************************************ ******* Forum Note: Use "Reply" to post a response in the discussion forum. ************************************************************************ ******* Forum Note: Use "Reply" to post a response in the discussion forum. ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.
On 1/3/07, Robert Roussey(MIS) <Robert.Roussey@spiritair.com> wrote: > I do not believe you can create a unique index on a varchar column. I do not believe you will find this limitation documented because I don't believe it is a real restriction. -- Jonathan Leffler #include <disclaimer.h> Email: jleffler@earthlink.net, jleffler@us.ibm.com Guardian of DBD::Informix v2005.02 -- http://dbi.perl.org/