Oracle Tips

You Asked
Hi, I searched in the archives for my question but I was unable to find an answer and I do apologize for asking what seems like such an easy question. I have always assumed that unique indexes were faster than non-unique indexes but I have never been completely sure of this. Scenario, I have table TEST with columns colA,colB,colC,colD,colE and with about 1.3 million rows. No two rows in this table have the same values for the 5 columns mentioned above. Here I can create a concatenated unique index or a non-unique index on the 5 columns mentioned above (unique indexes are also good for data integrity). Will the unique index be faster in a select query than a non-unique index here? Also in general, are unique indexes faster in select queries data than non-unique indexes? Thanks
and we said...
you know what the structural difference between a unique and non-unique index in Oracle is? Nothing. bit for bit, byte for byte, they would be the same. It is just that in a unique index, the rowid is not considered "part of the key" and in a non-unique index "the rowid is considered part of the key". But -- what is the performance difference? well, the optimizer can look at an index that is unique and say "ah-hah, if you use "where x =:x and y = :y and ...." I'm going to get ONE row back, I can cost that much better" If the index is non-unique, the optimizer will say -- "ah-hah, index range scan, I'm going to get 0..N rows back" and it'll cost it differently. So, a unique index will affect the generated plan -- it is more information for the optimizer to grab onto. If the data must be UNIQUE, you should use a UNIQUE constraint - not an index. We will take care of the index for you. If the constraint is not deferrable, we'll create a unique index for you. If the constraint is deferrable -- we'll use a non-unique index.