unique index on multiple columns

[manjunathk]

Hi friends,
I need to create an unique index on a set of 6 fields, which combinedly make a record as unique in one of our tables(there is no single field which is unique on its own).
I need to know, how does Oracle work when it creates the index or when validating the uniqueness. For example, let us say the index is on col1 to col6, in that order. Col1 has values which have the maximum chance to be unique and col6 has least chances. So, if a new record is inserted which has an unique value of col1, are the other columns still compared or not?
The main point here is that I need to select the order of the columns in the index. What will be the best order : "most unique chances" to "least unique chances" or it does not matter?
Thanks
manjunath

[marist89]

When creating a compound index, you should use the field that has the greatest number of unique values as the first column of the index. That being said, with six fields in your PK, you may want to look at using an artificial PK for this table.

[manjunathk]

Thanks Jeff,
We will be first testing the index on the 6 fields and I will also test the artificial Primary key.
Thanks again
manjunath

[chrisrlong]

Originally posted by marist89
When creating a compound index, you should use the field that has the greatest number of unique values as the first column of the index.

I've heard this same line repeated many times. Personally, I think it needs to have the following disclaimer: "All other things being equal" added to it.

Why? Because applicability of the index matters more than selectivity. You may pick field 5 to be the first field in the index because of its selectivity. However, what if you almost never include field 5 in the WHERE clause? You have a more selective index that will never actually be used. You can always fix your selectivity issue using a hint, but you can't force an index if you aren't using the left, leading columns. Just something to think about.

- Chris

[marist89]

Of course, I was assuming that because it is a PK that you would be doing DML against it where you would need to specify the exact PK. But, yes, if you are not using the most selective field, it would be a poor candidate for a PK or ANY index.

[nabaig]

Gurus,

What in god's name is a artificial PK?

Nizar

[marist89]

An artificial PK is a primary key in which it holds no meaning to the row in which it identifies. Yes, it violates normalization rules, but it has become a common practice among today's "modern" architects.

In this situation, the user needs six fields to form a natural PK. If he/she added a field called "id" and populated it with the value from a sequence, any joins would probably execute faster.

[chrisrlong]

Originally posted by marist89
Yes, it violates normalization rules, ...

I've heard others say this as well, but I just don't see it. I don't see how adding a surrogate key breaks any of the rules of normalization.

- Chris

[sonaliak]

One would still need a unique index if they want to make sure that the combination of all those values are unique, right ?
But may be you are right if you have a artificial PK the queries will be faster !
thanks