unique index vs non-unique index

Question:

What is between between “unique index vs non-unique index”. Which one is faster. The customer using non-unique and sql is getting delay If we change tp non-unique. Is it work ?

Answer:

Indexes can be unique or non-unique. Unique indexes guarantee that no two rows of a table have duplicate values in the key column (or columns). Non-unique indexes do not impose this restriction on the column values.

Oracle recommends that unique indexes be created explicitly, using CREATE UNIQUE INDEX. Creating unique indexes through a primary key or unique constraint is not guaranteed to create a new index, and the index they create is not guaranteed to be a unique index.

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”.

From Performance point of view:

The optimizer can look at an index that is unique and check, 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 perform , index range scan, he is 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.
Non-Unique indexes have various “overheads” when compared to Unique Indexes

Will examine two key differences today:

• Extra byte required per index row entry
• Index requires additional consistent reads and latch gets

Reading a Non-Unique Index is more expensive in terms of consistent reads and latches.