Re: Unique or non-unique indices

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? No, I don't think you missed it. The uniqueness of an index affects the selectivity of the possible query paths and consequently the cost the optimizer publishes. The optimizer chooses which index to use based on a number ranging from 0 to 1. This number is known as the "Selectivity". Please refer to the following extract out of the OL Dynamic Ser. Performance Guide. SELECTIVITY - the percentage of rows selected as a result of a filter EXPRESSION FORM SELECTIVITY indexed-col = literal val. F=1/(number of distinct keys in index) tb1.indx-col = tb2.indx-col F=1/(numb. of distinct keys in larger idx) Now that said think about this also: COST = # disk accesses + W * ( # tuples processed ) ( IO COST ) ( CPU COST ) W = weighting factor converting CPU cost to relative I/O cost ~ .03 With a unique index the est. number of tuples equal 1. I.E. lower cost Here is where I leave the book and enter the realm of logic, so if I'm wrong please someone correct me. With a query such as "select * from table-x where column-y = z" 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. Hope this helped. Kevin D. Perry (Still willing to learn) DBA ARGUS HEALTH SYSTEMS