Results 1 to 5 of 5
  1. #1
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1

    Unanswered: unique vs. non-unique indexes

    or should I say "indices"?

    Anyway, do you think there would be a noticeable difference in performance of an INSERT statement against a table with 5 indices if 4 of those indices are unique or non-unique?

    We have a table in a mixed OLTP/OLAP database (db2 v 8 fp 5 on Win2003) with about 150 million rows and significant number of inserts (say, few hundred thousand per day). In two of those indices cardinality is quite low: on the order of 30K to 100K.

    I'm thinking about ways to improve insert performance without sacrificing query execution times and was wondering whether it would be worth trying to make indices unique? Of course, testing would be the best answer but it takes about 6 hours to execute RUNSTATS on that table so I would like to begin with theoretical discussion.

    Thanks.

  2. #2
    Join Date
    Mar 2004
    Posts
    448
    check the size and dept of both indexes. I think that non-unique indexes occupies less space, one reason is that nulls are not part of the index and more rows are in the leaf pages less I/O.

    regards,

    mujeeb

  3. #3
    Join Date
    Mar 2004
    Location
    Toronto, ON, Canada
    Posts
    513
    Unique and non-unique indexes are fundamentally the same, though, they're both b-trees. I guess it would help the optimizer to know that it can only ever find a single matching value, other than that I don't see where there'd be any benefit?
    --
    Jonathan Petruk
    DB2 Database Consultant

  4. #4
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    I have never heard that non-unique indexes in DB2 occupy less space. I have heard that about some other databases though, but I am not sure that DB2 works that way unless it was changed from the original design (but my knowledge of internals is a bit dated).

    The problem with inserting in non-unique indexes is that DB2 has to find the end of the chain for that value to insert the next index row. If the cardinality is low (lots of the same index values) DB2 has to scan all the index entries for that value to find the end. Again, that may have been changed in recent releases (if IBM changed the internals of how indexes work), but I know that historically has been a problem.

    You don't necessarily need to get the index cardinality unique, but getting it down to below 200 does help. Also, making sure your percent free is set up in an optimal manner is important to prevent index page splits (which are also very expensive if done frequently).
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  5. #5
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by Marcus_A
    If the cardinality is low (lots of the same index values) DB2 has to scan all the index entries for that value to find the end.
    Yes, that's what I was thinking, too.

    Thanks for your thoughts. I'll run some experiments and share the results (if any).

Posting Permissions

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