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

>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. I read this couple of times and still could not make it. What exactly do you want. You can always enforce uniquness using triggers also. For starters, send the schema of the table in question and tell what is your requirement. Also mention what fields got to be unique as per your business requirement. >Two separate indexes is not a good alternative. Two indexes will >cause more locks when locking a row. Cud u explain this bit more. >It can make situation worse >than using table lock. when data changed, it will spend double time >to recreate whole indexes. What makes you think that having a huge 500 char index (if informix allows that) will have very fast. Informix locking is dependent on isolation level. I won't comment on number of locks till I see the application. Finally, locks by themselves is not an issue. May be it is an issue in Oracle bcos locks are stored as part of blocks and there can be only fixed number of locks in a block (fixed as in table configuration). But Informix acquires locks dynamically on the fly. > BTW, Maybe using last name and first name is a good idea for other > database, but not for informix, there u go again. Will u reframe the above. The worst you can say is that using last name/first name is not a good idea for your application. Don't say 'for informix', bcos I am also using informix and I always use first/last name separately. >So, I don't understand why informix has such limitation. I have an idea. Use Oracle or SQL Server and be happy with them. rk-

>>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. >I read this couple of times and still could not make it. What exactly >do you want. You can always enforce uniquness using triggers also. >For starters, send the schema of the table in question and tell >what is your requirement. Also mention what fields got to be unique as per >your business requirement. I don't want to enforce uiquness, I just want to make an index, for example, on a primary key. >>Two separate indexes is not a good alternative. Two indexes will >>cause more locks when locking a row. >Cud u explain this bit more. If you have two indexes on a table, you use row lock, you try to lock a row, actually, in informix database it will have a lock on the record and two more locks on the index. If you have three indexes, you will have three more locks. >>It can make situation worse >>than using table lock. when data changed, it will spend double time >>to recreate whole indexes. > >What makes you think that having a huge 500 char index (if informix >allows that) will have very fast. You can have a have huge 500 char index, but doesn't mean you must use it. Imagine a table has two columns (first name and last name). Although most people's first name or last name is not very long(but it doesn't mean there is no exception), you have to define varchar(255) for each column. In most cases, the two columns' total length is less than 100 characters. But for informix, you can't make a composite index on this two columns. Is it strange? >Informix locking is dependent on isolation level. I won't comment on >number of locks till I see the application. Finally, locks by themselves >is not an issue. May be it is an issue in Oracle bcos locks are stored >as part of blocks and there can be only fixed number of locks in a block >(fixed as in table configuration). But Informix acquires locks dynamically >on the fly. Why locks by themselves is not an issue? locks will eat shared memory, creating lock will occupy cpu time. It is a big problem. Actually, if database have too many locks, you should consider changing lock mode from row lock to page or table lock.