Re: Database design question

: >I have a table on a bought-in application with about 350,000 rows. It : >has a column defined as an index, but in fact the column contains : >contains nothing for any row. Therefore every one of the 350,000 rows : >has the same index value, which is clearly undesireable. However, what : >the design is clearly meant to achieve is fast selection of a single row : >when the column has a non-null value. : So you have several options: : 1) Leave it alone. : 2) Populate the column with unique values and see if the app takes : advantage of them. If so, good. If not, you have to change the app : to use them, which can be hard and might invalidate any warrantee : on the app from the vendor. : 3) Drop the column and the index, thus saving the space they now occupy. : See the warnings for 2) above. : 4) Check the app for statements which should be populating the column : but which are failing or not being executed. Same warnings. 5) Add another column to the index to make it a more unique composite index. Have you had performance problems? Performance on inserts and deletes will generally be... ummm... sub-optimal if you have a highly duplicate key. If you add another column AFTER this column, you will greatly improve insert/delete performance, while still leaving the index in place for when you (hopefully) will have non-null values and want to benefit from the index. June ---- June Tong Informix Software ---- ---- Senior Consultant (415) 926-6140 ---- ---- International Support junet@informix.com ---- ---- Location-du-jour: Menlo Park ---- * * Standard disclaimers apply * - Please do not send me requests/questions by mail. When I have the knowledge - and time permits, I try to answer questions on comp.databases.informix, but - travel schedule, time, and volume make responding to personal requests - difficult and often slow. Please call your local Informix Technical Support - organization for assistance with technical issues.