Re: Easy primary key question

sldcrew2k_at_yahoo.com (steve deno) wrote in message news:<8e6b14d8.0211231115.3b6e6be2_at_posting.google.com>...
> I think this is pretty basic, but: How does the order in which the
> fields in a composite primary key on a table affect the
> performance/effectiveness of the key? Is it based on the relative
> selectivity of the fields?
>
> Thanks

The answer is, it depends!

Do you have queries that provide only 1 column of the multi-column key? If so, then prior to version 9 you would probably need to place that field first in the key in order for the optimizer to be able to use the PK index to solve that particular query. With version 9 then perhaps the CBO can use a skip scan of the index for the same query.

Then there can be benefits from having the least selective columns first if index compression is in use. But then again I have seen trace files that clealy show performance degradation can occur when compression is used on certain indexes so you have to be careful and test the index when you have realistic data to be certain you get the expected benefits.

I encourage the developers I work with to order any multi-column index's columns based on the which columns have values provided most often in query where columns. This maximizes the odds the CBO will be able to use the index for the multiple queries, which saves from having to create extra indexes just for one query.

HTH -- Mark D Powell -- Received on Sat Nov 23 2002 - 18:34:48 CST