So, Helen, if an index use the column A, then (ideally) the column A should
not be updatable?

That's new for me.

Greetings.

Walter.

On Wed, Feb 13, 2013 at 8:28 PM, Helen Borrie <helebor@...> wrote:

> **
>
>
> 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
> __________________________________________________________
>
>
>


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