On 15-10-2012 18:14, Leyne, Sean wrote:

> Rick,
>
>> What is the effective difference between these two key elements
>> orderings?
>>
>> ALTER TABLE T ADD CONSTRAINT U UNIQUE (PK, HIGH_MAX_DUP)
>>
>> ALTER TABLE T ADD CONSTRAINT U UNIQUE (HIGH_MAX_DUP, PK)
>>
>> The first version is how I normally order key elements, from most unique to
>> least. Since a primary key index exists, the second would allow
>> HIGH_MAX_DUP to be used in queries. But would ordering the key
>> elements like this have any impact due to the high number of duplicates (it's
>> almost as bad as an even split between Boolean values).
>
> Personally, I always work from generic to most specific.
>
> I have always had the impression that it produces the most compact indexes since the engine will use "prefix compression" to group index items together. (I am trying to explain the general approach -- the description is not technically correct)

That doesn't sound right to me, but I am sure that Ann will jump in and
educate me (us). I think you want the most specific column first in the
index, as you want to narrow down the search-space quickly. Using the
least specific column first is (almost) equivalent to using a low
selective index.

Mark
--
Mark Rotteveel