On Mon, Oct 15, 2012 at 12:09 PM, Rick Debay <rdebay@...> wrote:

> 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).
>
>

The key will be smaller if you put the field with lots of duplicates first.
Firebird does prefix compression, so the leading part of the key that
duplicates the previous value is not stored. The example below show how
five different key values would be stored, assuming that 00000 is the first
in its page or sector.

00000 is stored as suppressed length 0 key 0000
12345 is stored as suppressed length 0 key 12345
12346 is stored as suppressed length 4 key 6
12346 is stored as suppressed length 5 no key
12356 is stored as suppressed length 3 key 56.

I'd choose ALTER TABLE T ADD CONSTRAINT U UNIQUE (HIGH_MAX_DUP, PK)

Good luck,

Ann


[Non-text portions of this message have been removed]