Re: Unique or non-unique indices

Kevin Perry wrote: > > MIS Dept <whsmis1@bellsouth.net> wrote: > : Here's an indexing question: > > : I am in the process of adding a few indices to a given table; the table > : itself consists of over 3 million rows. I've had a tendency to just > : 'create index' without thought to the uniqueness of the index. If these > : indices are unique, is there any benefit to defining them as such? > > : It appears that the optimizer doesn't really care if the index is unique > : or not. I did some testing and in some instances my cost actually > : increased, slightly. > > : Did I miss something here? Kevin Perry wrote: > A unique index on column-y would have only to do one lookup. If it is > nonunique a total index or sequential scan would be needed to find all > applicable rows. I.E. more work even though there may be only one row > satisfying the query. There are several issues and some are version specific. 1) Unique indexes will enforce uniqueness in the table. 2) Since Informix uses an inversion index for non-unique indexes, ie one index node with a list of rowids containing that duplicated key, if the real key is actually unique the create index will create a SLIGHTLY more efficient index assuming one rowid per key value. (This also has implications for indexes that have a high duplication ratio). Because of this inverted list approach, Kevin Perry's comments about a sequential scan or multiple index lookups being required if the index is not created unique are incorrect. Only if the key was indeed not unique and the number of duplicate rowid took up more than a full node (as in a high duplication ratio index) would multiple index reads be needed. 3) For 5.0x which does not keep distributions, or 7.xx with statistics updated low or not at all, the optimizer does not know that a search will return only one row if the index is not created as a unique index. In this case the worst result will be that the optimizer selects another, perhaps, suboptimal, index where the probability of multiple. index reads, ie the estimated cost, is lower. In 7.xx with statistics medium or high the optimizer can at least guess that at worst only a few rows will contain the searched for key. In this case chosing the 'wrong' index is unlikely. The real costs to using a non-unique index for a unique key are: 1) Slightly increased storage requirements. 2) Slightly increased actual cost, though perhaps the same estimated cost. Art S. Kagel