Unique vs non-unique index

Question

My table holds nearly 40+ million records., with DML.

Would like to know which one of these Unique or non-unique index would be better option for implementation. To give better performance.

Environment : Oracle 11g

Answer 1

If possible, use a unique index. Otherwise, use a non-unique index.

In other words, if you can use a unique index, you should. If there is any reason why duplicate data might need to be recorded, you don't have a choice anyway.

If the index is guaranteed unique, Oracle can perform certain query plan optimisations that are not possible with a non-unique index - for example, if a unique index is used to probe for a particular value, Oracle can stop processing further index blocks as soon as it finds a match.