is there any performance difference between unique and no unique index

tal_olier · #1 (**permalink**) 07-03-03, 08:30

Hi,

We are working with both databases Oracle and MS-SQL and we have an open channel to MS-SQL server core team.

We got the word from them that there is a difference between unique and no unique index from the optimizer perspective, i.e. the optimizer knowing that the index is unique may choose a more suitable plan for running a specific query, we're still trying to find a specific example.

Anyway, since we do not have an open channel to Oracle core, I thought maybe one of you guys knows something about Oracle optimizer behavior on this issue.

I am interested verified answers from certified sources and not suggestions or thoughts.

Thanks in advanced,

Tal (otal@mercury.co.il).

blom0344 · #2 (**permalink**) 07-03-03, 09:07

" verified answers from certified sources "

" not suggestions or thoughts. "

Why post this in an open forum anyway then?
I will give you my thoughts anyway (for what is is worth)

Column contents will decide whether you can define a unique index, ultimately the SQL conditions will determine what is most effective.
Accessing the greater part of a table can be even faster by doing a full table scan. There is no ultimate theory or truth here, just going for performance testing / using explain plan to get as much insight as possible...

tal_olier · #3 (**permalink**) 07-03-03, 09:32

Quote:

Originally posted by blom0344
" verified answers from certified sources "

" not suggestions or thoughts. "

Why post this in an open forum anyway then?
I will give you my thoughts anyway (for what is is worth)

Column contents will decide whether you can define a unique index, ultimately the SQL conditions will determine what is most effective.
Accessing the greater part of a table can be even faster by doing a full table scan. There is no ultimate theory or truth here, just going for performance testing / using explain plan to get as much insight as possible...

Did not meant to offend.

Thanks.

Tal.

bstjean · #4 (**permalink**) 07-03-03, 11:32

Quote:

Originally posted by tal_olier
Hi,

We are working with both databases Oracle and MS-SQL and we have an open channel to MS-SQL server core team.

We got the word from them that there is a difference between unique and no unique index from the optimizer perspective, i.e. the optimizer knowing that the index is unique may choose a more suitable plan for running a specific query, we're still trying to find a specific example.
Tal (otal@mercury.co.il).

Yes because a unique index has a better selectivity and since selectivity is one of the things the optimizer looks at... But there's more that that. You can't say for sure that it's always better... Depends on a lot of things... For instance, if your table has 2 rows, neither of these 2 indexes will be used since a table scan will be way more faster. But in 99.99% of the cases, a unique index is better than a "not unique" one.