Re: Database design question

At 21:52 20-11-96 +0000, you wrote: >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. > >Any hints or tips for this? Learn more about the data model: The manual "The Informix Guide to SQL Tutorial" has a good chapter on building a data model. It discusses, in "everyday" terms, the characteristics of a good primary key. The following chapter, "Implementing the Model" then goes on about the data types and discusses the serial type, which may be what your application intended. If you purchased the application, and are satisfied with its performance and its storage requirements, then you may be smart to leave that column alone. It can be hard to identify the ramifications of changes to an "unknown" application. 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. You should check the query plans of the SQL statements that use that table, checking for queries that try to use that index but are forced to perform a sequential scan instead. You can do this using the SET EXPLAIN ON feature of I-SQL, as explained in the SQL Tutorial manual. There is another, unsupported way to check query plans using the SMI that was detailed in this forum a few weeks ago. You may find more details in the $INFORMIXDIR/etc directory in the buildsmi script. The supported parts of that structure are described in the "Informix-OnLine Dynamic Server Administrator's Guide" volume 2, version 7.1 in chapter 39. That should keep you busy for a while. ______________________________________________________ Clem Akins (aka clema@informix.com) Informix Software, Inc (Standard disclaimers apply) International Technical Support Last seen: Singapore, home of the Merlion