At 12:26 AM 16/03/2008, you wrote:

>> What duplicate index?
>
>I have the primary key and then the foreign key constraint causes
>FireBird to create another index where it could use the primary key as
>well.
>
>SPLIT (133)
> Primary pointer page: 144, Index root page: 145
> Data pages: 41732, data page slots: 41732, average fill: 59%
> Fill distribution:
>
> Index FK_SPLIT_SWIMRESULT (1)
> Depth: 3, leaf buckets: 4903, nodes: 12644728
> Average data length: 0.00, total dup: 8582539, max dup: 199
> Fill distribution:
>
> Index PK_SPLIT (0)
> Depth: 3, leaf buckets: 6999, nodes: 12644728
> Average data length: 3.00, total dup: 0, max dup: 0

There is no duplicate index.

CONSTRAINT PK_SPLIT PRIMARY KEY (SWIMRESULTID, DISTANCE)

(PK forms an index with two key segments)

ALTER TABLE SPLIT ADD CONSTRAINT FK_SPLIT_SWIMRESULT
FOREIGN KEY (SWIMRESULTID) REFERENCES SWIMRESULT
(SWIMRESULTID)

(FK forms an index with one key segment)

However, the existence of both of these indexes does give the optimizer the opportunity to select either index for certain operations, because of its ability to use the first segment of a composite index as though it were a single-segment index. It would be desirable for it to use the PK's index, because it is unique. You would need to watch the plans and, if necessary, force a plan to use it.

I don't like this design much because I don't like non-atomic indexes and I don't like hierarchical primary keys. But it's a different issue, I guess.

./heLen