Re: Index with duplicate values

First of all, It is recommended not to have an index on a column with few different values. What type of locking do you have in that table? -----Original Message----- From: David K. Killough <killougd@ix.netcom.com> To: informix-list@iiug.org <informix-list@iiug.org> Date: Viernes 18 de Septiembre de 1998 08:01 PM Subject: Index with duplicate values >Hi. > >We have a table that has 2,500,000 rows. We added an index on a column that >only has a handful of values: say for example: > >"ABC" - 2,499,000 rows >"DEF" - 200 rows "GHI" - 200 rows "JKL" - 200 rows "MNO" - 200 rows >"PQR" - 200 rows > >I am suspicious that this may cause problems: >1) With a 4K page size it seems that I'll get around 585 rows per page in >the index. For the "ABC" column I think I'll fill about 4,271 pages with the >same value. When updating a row to set this column to an "ABC", I'm not >sure how it impacts performance and concurrency. After we made it, we >started getting strange locking problems later that day that we could not >explain. I was called that night because a different index had become >corrupt and had to be rebuilt. Although it wasn't the index we made, it was >a composite index that had the same first column. Also the message said >somthing like "key: too many parts or too long." Are my problems related to >my index?? Could this cause corruption?? > >Thanks, Dave Killough > > >