Results 1 to 3 of 3
  1. #1
    Join Date
    Oct 2002

    Unanswered: Performance of Unique vesus Non-Unique Indexes

    I know this is going to be a subjective question and will probably raise the ire of more than a few folk, but here goes:

    Having read Why low cardinality indexes negatively impact performance
    specifically viewing Figures 2 & 3, if I am able to ensure referential integrity by other means, am I going to see better read performance from a Unique or a Non-Unique index given the structure of the way they seem to store the keys leaf pages ?

    Seems to me that a Non-Unique index would get appear to get much better read performance due to storing multiple RIDs per leaf page no, or am I simply clutching at straws here ?

    Thanks for any comments.

  2. #2
    Join Date
    Apr 2008
    Iasi, Romania
    Provided Answers: 2
    Quote Originally Posted by tembo View Post
    if I am able to ensure referential integrity by other means
    Declaring a FK constraint and creating an index on that column are different things. Usually, it is recommended to have an index on the column used for FK, but this is not a constraint.
    My conclusion: do NOT ensure RI by other means
    Florin Aparaschivei
    DB2 9.7, 10.5 on Windows
    Iasi, Romania

  3. #3
    Join Date
    Dec 2007
    Richmond, VA
    Provided Answers: 5
    This has been covered quite a bit over the years.
    or if you just want to see in regards to DB2

    Also, low cardinality indexes are not always a bad thing. It can depend on data skew as well. I have created plenty of indexes on a Y/N flag or similar, when the skew of data is around 99.5% one value and .05% the other and I am always looking for the other.

Posting Permissions

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