why total width of all index can't exceed 255?????

I don't understand why informix has such limitation. Imagine, you need a compound index, one column's type is varchar(255), then you can't create compound index with this column. Is it very strange???

Why would you wish to create an index on a varchar column? How efficient would said index be? Not very, it seems to me. But then, that's my opinion. -----Original Message----- From: JACK [mailto:jackleeok@yahoo.com] Sent: Thursday, November 20, 2003 10:20 PM To: ids@iiug.org Subject: why total width of all index can't exceed 255????? [2209] I don't understand why informix has such limitation. Imagine, you need a compound index, one column's type is varchar(255), then you can't create compound index with this column. Is it very strange??? "CONFIDENTIALITY NOTICE: This message originates from WHSmith USA Travel Retail. This email message and all attachments may contain legally privileged and confidential information intended solely for the use of the addressee. If you are not the intended recipient, you should immediately stop reading this message and delete it from the system. Any unauthorized reading, distribution, copying, or other use of this message or its attachments is strictly prohibited. All personal messages express solely the sender's views and not those of WHSmith USA Travel Retail. This message may not be copied or distributed without this disclaimer."

? When a serial column can index over 2billion? in 4 bytes? What are you trying to index - all the grains of sand on the beach? You should get a different programmer to help you out - the one you currently have seems a bit on the stoooopid side of the fence. JACK wrote: > I don't understand why informix has such limitation. > Imagine, you need a compound index, one column's type is varchar(255), then you can't create compound index with this column. Is it very strange??? > > > -- ( ______ )) .-- Scott MacKenzie; Dine' College ISD --. >===<--. C|~~| (>--- Phone/Voice Mail: 928-724-6639 ---<) | ; o |-' | | \\\\--- Senior DBA/CARS Coordinator/Etc. --/ | _ | `--' `-- E: scottm at dinecollege dot edu -' `-----'

You must be running an old system - possibly 7.31. The limit is currently 390 bytes in IDS 9.40 (and 9.30, and I'm not sure when it was increased). -- Jonathan Leffler (jleffler@us.ibm.com) STSM, Informix Database Engineering, IBM Data Management 4100 Bohannon Drive, Menlo Park, CA 94025 Tel: +1 650-926-6921 Tie-Line: 630-6921 "I don't suffer from insanity; I enjoy every minute of it!" |---------+----------------------------> | | "JACK" | | | <jackleeok@yahoo.| | | com> | | | Sent by: | | | forum.subscriber@| | | iiug.org | | | | | | | | | 11/20/2003 07:20 | | | PM | |---------+----------------------------> >------------------------------------------------------------------------------- ----------------------------------------------------------------| | | | To: ids@iiug.org | | cc: | | Subject: why total width of all index can't exceed 255????? [2209] | >------------------------------------------------------------------------------- ----------------------------------------------------------------| I don't understand why informix has such limitation. Imagine, you need a compound index, one column's type is varchar(255), then you can't create compound index with this column. Is it very strange???

Why SQL Server Oracle and other databases have no such limitation. Do you mean they are mad? And why Informix makes the limitation to 390 bytes in 9.40. Are they also mad? I am a programmer. I don't think this limitation is reasonable. If you have a table which contains user name and user sex coulmn. And only this two columns can decide a unique user. How can you make a index for this table. Remember, user name can be 1 characters or up to 255 characters. By the way I guess you are a student or dba. Am I right.

>Why SQL Server Oracle and other databases have no such limitation. Do you mean they are mad? >And why Informix makes the limitation to 390 bytes in 9.40. It would be interesting to see how many databases in Oracle/SQl server use such a big index. IMO any index as big as 255 char is a big performance drag. >I am a programmer. I don't think this limitation is reasonable. If you have a table >which contains user name and user sex coulmn. And only this two columns can decide >a unique user. How can you make a index for this table. Remember, user name can be >1 characters or up to 255 characters. >By the way I guess you are a student or dba. Am I right. Ah!!! No wonder u r arguing so stupidly. No one would keep a name as a single field. I would keep first name and last name separately so to make a quick scan on last name.

I don't know where you get this ridiculous idea. Is last name unique? Why do you choose only last name as the index, At least you need last name and first name as index. How can you make it in a comppound index in informix. I wonder if you have a good education. It seems like you only know word 'stupid'. If you don't know other thing, please shut up. At least, others won't know you know nothing.

Jack I wouldn't start throwing insults too freely, especially at such knowledgable people as Ravi. A person's name never makes a good primary key candidate (whether as a combined name, or as two separate fields in a combined index) because it can never be guaranteed to be unique. Even combining with gender will make no difference (all the John Smith s I know are male !!). Your 'person' table should have a serial field as its primary key which is used to reference this table from any other table in the database. First_name and last_name fields should be separate (it is far easier to manipulate like this is you need to output in reverse order etc.) with an index (allowing duplicates) on each. Indexing gender is a waste of disk space, by the time you have identified half your table from the index and then read the data the engine might just as well have scanned the entire table. I suggest you invest in a Relational Database Design manual or look at the Informix Design and Implementation Guide at http://publibfi.boulder.ibm.com/epubs/pdf/ct1stna.pdf Keith -> -----Original Message----- -> From: JACK [mailto:jackleeok@yahoo.com] -> Sent: Sunday, November 23, 2003 4:48 AM -> To: ids@iiug.org -> Subject: Re: Re: Re: why total width of all index can't -> exceed 255????? -> [2225] -> -> -> I don't know where you get this ridiculous idea. Is last -> name unique? Why do you choose only last name as the index, -> At least you need last name and first name as index. How can -> you make it in a compound index in informix. -> -> I wonder if you have a good education. It seems like you -> only know word 'stupid'. -> -> If you don't know other thing, please shut up. At least, -> others won't know you know nothing. -> ******************************************************************************** ** This message is sent in strict confidence for the addressee only. It may contain legally privileged information. The contents are not to be disclosed to anyone other than the addressee. Unauthorised recipients are requested to preserve this confidentiality and to advise the sender immediately of any error in transmission. This footnote also confirms that this email message has been swept for the presence of computer viruses, however we cannot guarantee that this message is free from such problems. ******************************************************************************** **

>I don't know where you get this ridiculous idea. Is last >name unique? This is a basic database design question. Nothing to do with Informix. I am afraid by asking such basic question you are only exposing your ignorance of a good design. eh????. Who is talking about unique index. I am talking about duplicate index. The reason to keep last name separate from first name is for quick search. Have you ever been to a drug store or called 800 number for support. They start with searching last name. If first and last name are in a single field, the only way to search the last name is where last_name matches "*SMITH*". Keeping a last name a separate field can help in last name lookup very fast and to narrow it down, you can lookup on first name plus last name also. >Why do you choose only last name as the index, >At least you need last name and first name as index. How can >you make it in a compound index in informix. No. You can have a separate index on first name and last name and each can be upto 255 character long. In my 10+ yrs of working with Informix, I am yet to stumble upon a situation where I felt that 255 characters is a limitation for creating index. But then what do I know ... Informix has other limitations, some of them quite annoying. But not this one.

I don't really want to insult anyone. especially in a forum which is for discussing technology. How can I be rude to anyone who really want to give me his opinion. I confess the example is not good. but the real situation is there have two names which can decide a unique record(this is Spec, we can't change this rule). We have different id for each record, but id is for internal use, is transparent to user. So user can only give two names to get the unique record. In this case, serial field can't help. I can't use two names to find a serial field and use this serial field to get a record, can I? As user always use two names to find a record, so combined index is the only choice. Two separate indexes is not a good alternative. Two indexes will cause more locks when locking a row. It can make situation worse than using table lock. when data changed, it will spend double time to recreate whole indexes. BTW, Maybe using last name and first name is a good idea for other database, but not for informix, because i can't use combined index under this situation. As I mentioned, two separate indexes is also not a good choice. So, I don't understand why informix has such limitation. Jack

>This is a basic database design question. Nothing to do with >Informix. I am afraid by asking such basic question you are >only exposing your ignorance of a good design. Maybe it is a good and basic database design pattern. But There is no always good design pattern under every situation. >eh????. Who is talking about unique index. I am talking about >duplicate index. The reason to keep last name separate from >first name is for quick search. Have you ever been to a drug >store or called 800 number for support. They start with searching >last name. If first and last name are in a single field, the only >way to search the last name is where last_name matches "*SMITH*". >Keeping a last name a separate field can help in last name lookup >very fast and to narrow it down, you can lookup on first name plus >last name also. Why do I need to have duplicate index if I can have unique index. the example maybe is not good. But in my case, I only need using the name (the last name and the first name) to find the record. I don't need to use last name or first name to find records. So, separating name to two columns is no meaning to me. >In my 10+ yrs of working with Informix, I am yet to stumble >upon a situation where I felt that 255 characters is a limitation >for creating index. But then what do I know ... >Informix has other limitations, some of them quite annoying. But >not this one. I have ten years of experience on programming including four years on database programming. I developed progams for database with c, c++ and java, but I never met a situation like this. My program works fine with oracle, sybase, sql server and so on but not with informix. Informix is too special. The reason using index, is to avoid get error message when a row was locked and another session try search whole table to find matched records. (The solution is suggested by many people on the web. Although it sounds strange to me. why program can't work with database without index).