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)


Sean