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.
