Unique vs Nonunique indexes

[jdorlon]

Hi everyone -

I've read that unique indexes perform better than nonunique indexes.

Now, in an app where I do not have the source code, I have found some indexes that are classified as 'nonunique', but every row in the index is indeed unique.

Can I expect better performance out of this index if I drop it and recreate it as unique? Or does it perform as well as a unique index now because it actually is unique even though it is not defined in the data dictionary as such.

The index is quite large so I don't want to just 'do it and see'.

thanks for your help!

-John

[gpsingh]

ARe u using 8.1.6 , then check whether the primary key constraint or unique constraint was created deferrable .
If yes, u need to find out the reason from developers.

The reason unique performs better is because it uses less space , but now in 8.1.6 u can compress the indexes.

[adrianomp]

It's strange that you found an non-unique index over a unique column. When you define a unique (or primary key) constraint, Oracle implicitly creates an unique index with the same name of the constraint. The non-unique indexes you found probably were related to another columns (or combination of columns), since it's impossible to have more than one index on the same column.

Just an add, Oracle reccomends that you do NOT create unique indexes explicitly, and let the work to be done automatically. But, if you do it, Oracle will use default settings to the index, what could be a bad thing. So:

1- Create Unique index with particular settings;
2- Create Unique constaints on table.

If you swap these steps, you won't be able to customize your index.


Adriano.

[jdorlon]

Hi Again,

Sorry, maybe my first post was misleading.

The index does not have an associated constraint of any kind. I know that the index is considered nonunique because that's what it says in DBA_INDEXES. I know what the data in that index actully is unique, though, because after an ANALYZE..COMPUTE on that index, the number of DISTINCT KEYS is the same as the number of rows in the table....

OK, if it will consume less space as a unique index that is a good reason to change it. How much do you think it will shrink? It is a 3 column index with about 3 million distinct keys. The column types all NUMBER type (with no specified precision)

Thanks!
John

[marist89]

This may be a case of the designers will allow duplicate values in this column, but that's not the way you are using it. By putting a unique index on this field, you may be putting a constraint on the application that was not intended. Since it's a third-party app, I would just leave it alone...

[jdorlon]

Incase anyone is curious, I tried it in a development environment to see what the difference would be.

As a Non-unique index, it occupied 90Mb.
as a Unique index, it occupied 86Mb.

So we're looking at about 4 or 5 percent.

this is a 3 column index - columns of type NUMBER

-John