CODEWITHSUNDEEP
sqloracle-databaseplsql
BalaB
BalaB

Reputation: 3871

Unique vs non-unique index

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

Upvotes: 11

Views: 22042

Answers (2)

Jeffrey Kemp
Jeffrey Kemp

Reputation: 60262

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.

Upvotes: 22

Ashish sinha
Ashish sinha

Reputation: 148

this entirely depends on type of the data in the table if it has data where uniqueness is

high then use B-tree else use Bitmap index.

Upvotes: -5

Related Questions