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 __________________________________________________________________
