At 12:44 p.m. 14/02/2013, Ruben Marti wrote:

>I have a table with several hundreds of thousands of records that has a
>composite primary key, with the fields "START_DATE" and "ITEM".
>
>I honestly do not know if it's interesting declare the index as "UNIQUE"
>when it can not have duplicate records.

No use at all.

>For optimization reasons, I need an index composed of the fields "ITEM" and
>"START_DATE".

You should get optimal performance by creating a simple index on START_DATE. The optimizer will use the PK index if it has to search on ITEM alone.

>What will I gain or loss if the index is declared "UNIQUE"?

No gain. Possible confusion for the optimizer. It already knows that combination is unique.

>What type (UNIQUE or NOT UNIQUE) is faster to retrieve the data?

Irrelevant.

>What type (UNIQUE or NOT UNIQUE) is faster to insert the data?

Irrelevant.

>What type (UNIQUE or NOT UNIQUE) is faster to update the data?

If START_DATE is an updatable field, then you have a design flaw that is likely to hurt performance and also make your data vulnerable to invisible corruption by users, regardless of whether the index is unique or not.

./hb


Helen Borrie, Support Consultant, IBPhoenix (Pacific)
Author of "The Firebird Book" and "The Firebird Book Second Edition"
http://www.ibphoenix.com/products/books/firebird_book
__________________________________________________________________