Results 1 to 6 of 6
  1. #1
    Join Date
    Aug 2009
    Posts
    42

    Unanswered: Index on predicates

    I have a query with the following predicate

    where num = ?
    and id = ?
    and (code_a = ? or code_b = ?)


    Is it better to create one index on: num, id, code_a, code_b


    or two indexes:
    1) num, id, code_a
    2) num, id, code_b

  2. #2
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Depends somewhat on cardinality of the values, but in most cases the one index would be best for this particular query.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  3. #3
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    Actually, The two index approach along with a rewrite of the query would work best. Depending on cardinalities of columns as was already mentioned.

    Code:
    select ...cols   from ...
    where num = ? 
      and id = ?
      and code_a = ?
    union all 
    select ...cols   from ...
    where num = ? 
      and id = ?
      and code_b = ?

    Dave

  4. #4
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Quote Originally Posted by dav1mo View Post
    Actually, The two index approach along with a rewrite of the query would work best. Depending on cardinalities of columns as was already mentioned.
    That is a pretty big waste of space and negative impact on inserts, updates, and deletes to have both of those indexes with first two columns the same on both indexes. All things considered, having one index is probably best in this situation.
    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
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    It will depend on the situation, but is very common practice as well. Normally, you don't have a case with the first column being the same it is the two different predicates that are in the indexes, such as say, a member_id or usr_nme. Also, we don't know the cardinality of num, it could be 2 or something along those lines.

  6. #6
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Quote Originally Posted by dav1mo View Post
    Also, we don't know the cardinality of num, it could be 2 or something along those lines.
    Or it could be the opposite. Generally columns like num and id have a large number of values and codes have relatively few. How much do you want to bet on this one?
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

Posting Permissions

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