Results 1 to 5 of 5
  1. #1
    Join Date
    Jan 2003
    Posts
    6

    Question Unanswered: index definition

    Hi, I have a table with 2 indexes, the first one defined like:

    column1, column2, column3

    and the second one

    column1, column2

    I am not sure if a can eliminate index 2. I supouse that, if index 1 has column1 and column2, maybe DB2 could use index one to cover both cases, I mean, on a where clause, DB2 has to match the exact columns of an index to use it or just the first columns ?

    Thanks

  2. #2
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    You should definitely drop the second index, it is redundent. If the second index was on (column2, column3) that would be different.
    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
    Nov 2003
    Location
    Germany
    Posts
    62
    The second index is only redundant, if it is not unique, but if it is unique, you will loose the uniqueness on these two columns. So, may be the first index is redundant or only makes sense, if there are queries, where the access path becomes index only.

  4. #4
    Join Date
    Nov 2003
    Location
    Netherlands
    Posts
    96
    Originally posted by Walter Janissen
    The second index is only redundant, if it is not unique, but if it is unique, you will loose the uniqueness on these two columns. So, may be the first index is redundant or only makes sense, if there are queries, where the access path becomes index only.
    You're right, but it's also possible to include columns in the index which are not part of the index key.

    CREATE UNIQUE INDEX INDEX1 on TABLE1 (COLUMN1, COLUMN2) INCLUDE (COLUMN3)

    which makes the second index redundant (this might be different on DB2 OS/390)

  5. #5
    Join Date
    Nov 2003
    Location
    Germany
    Posts
    62
    Yes this is different for DB2 z/OS. Even in Version 8 that will not be supported.

Posting Permissions

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