RE: Index with duplicate values

Informix recommends that you avoid highly duplicate indexes. The following excerpt from the IDS 7.2 "Managing and Optimizing" class indicates why: "When an entry has to be deleted from a list of duplicates, the server has to read the whole list and rewrite some part of it. When adding an entry, the database server puts the new row at the end of the list. Neither operation is a problem until the number of duplicates values becomes very high. The server is forced to perform many I/O operations to read all the entries, in order to find the end of the list. When it deletes an entry it will typically have to update and rewrite half of the entries in the list. When such an index is used for querying, performance can also degrade, because the rows addressed by a key value may be spread out over the disk. Imagine an index addressing rows whose location alternates from one part of the disk to another. As the database server tries to access each row via the index, it must perform one I/O for every row read. It will probably be better off reading the table sequentially and applying the filter to each row in turn. If it is important to index a highly duplicate column, you may consider forming a composite key with another column that has few duplicate values" Depending on your Informix software (SE, IDS, XPS) and version, which you did not mention, you may also be encountering a software bug that caused corruption. -----Original Message----- From: David K. Killough [mailto:killougd@ix.netcom.com] Sent: Friday, September 18, 1998 18:46 To: informix-list@iiug.org 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