Results 1 to 3 of 3

Thread: index question

  1. #1
    Join Date
    Oct 2005
    Posts
    26

    Unanswered: index question

    I have programs that will utilize index I. I also have programs that will utilize index J. Can I combine the the two indexes into like the example index K below (and both pograms be happy)?

    Index I:
    Column A
    Column B

    Index J:
    Column A
    Column C

    Index K:
    Column A
    Column B
    Column C

    Thanks,

    CougarTrace
    Last edited by cougartrace; 05-17-06 at 12:33. Reason: better explanation

  2. #2
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Index I is redundant based on the existence of index K.

    Index J should be turned around to be:
    Column C
    Column A

    But it is possible that index J is also redundant if the cardinality of Column A is high enough on index K.
    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 2005
    Posts
    273
    I'd not advice to combine the two indices.

    If you have a WHERE-Clause like:
    WHERE A = .... AND C = ....
    the "new" index K has only 1 matching column ( instead of 2, which Index J has ) and that will result in more get-page-requests and a poorer performance.

    Programs that use Index I ( with a WHERE-CLause :
    WHERE A = ... AND B = .... )
    wouldn't be happy too. The additional column might require to read more index-pages.


    What would benefit from the combination ?
    - each insert / delete / update - as only one index has to be updated
    - a SELECT c FROM ... WHERE a = ... AND b = ... - as the query is index-only

Posting Permissions

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