unique index on multiple columns
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 9 of 9

Thread: unique index on multiple columns

  1. #1
    Join Date
    Oct 2000
    Posts
    211
    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

  2. #2
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    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.
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  3. #3
    Join Date
    Oct 2000
    Posts
    211
    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

  4. #4
    Join Date
    Nov 2000
    Location
    Baltimore, MD USA
    Posts
    1,339
    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

  5. #5
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    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.
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  6. #6
    Join Date
    Aug 2000
    Posts
    236
    Gurus,

    What in god's name is a artificial PK?

    Nizar

  7. #7
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    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.
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  8. #8
    Join Date
    Nov 2000
    Location
    Baltimore, MD USA
    Posts
    1,339
    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

  9. #9
    Join Date
    Jan 2001
    Posts
    318
    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
    Sonali

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width