Results 1 to 6 of 6
  1. #1
    Join Date
    Sep 2014
    Location
    Long Island, New York
    Posts
    14

    Unanswered: DB2 using wrong index

    Hi,

    Can someone explain why DB2 is selecting one index over another. We are using DB2 9.7 on AIX.

    CREATE INDEX INDEX1
    ON SPEC_TEST
    (ACC_NUM_YR ASC,
    ACC_NUM" ASC);

    CREATE INDEX INDEX2
    ON SPEC_TEST
    (ACC_NUM ASC,
    ACC_NUM_YR ASC,
    TST_SEQNUM ASC);

    Select * from spec_test where acc_num = '1234567' and acc_num_yr = 2014 and tst_seqnum = 4;

    When I look at Visual Explain, DB2 is selecting the INDEX1 index over the exact match of INDEX2 index. Now, here's what makes it even stranger, this is only happening at a couple of client sites, others are working as expected. They all have the exact table and index structure. Any ideas?
    Thanks
    Attached Thumbnails Attached Thumbnails spec_test.bmp  

  2. #2
    Join Date
    Apr 2008
    Location
    Iasi, Romania
    Posts
    561
    Provided Answers: 2
    I guess it depends on TST_SEQNUM cardinality. When this is low, it may be faster to search into an index with fewer nodes.
    However, you shoud check if statistics are up-to-date
    Florin Aparaschivei
    DB2 9.7, 10.5 on Windows
    Iasi, Romania

  3. #3
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    why do you feel its the wrong index? The size may have changed and therefore you have more index pages to access and you have to go to the data page anyway since you have select *. Have you tried forcing DB2 to use the index you feel is the "right" one to see if there is a difference in your execution time?
    Dave

  4. #4
    Join Date
    Apr 2008
    Location
    Iasi, Romania
    Posts
    561
    Provided Answers: 2
    Quote Originally Posted by dav1mo View Post
    Have you tried forcing DB2 to use the index you feel is the "right" one
    Now I'm curious. How can I do that (on LUW especially)?
    Florin Aparaschivei
    DB2 9.7, 10.5 on Windows
    Iasi, Romania

  5. #5
    Join Date
    Apr 2012
    Posts
    1,034
    Provided Answers: 18
    Quote Originally Posted by aflorin27 View Post
    Now I'm curious. How can I do that (on LUW especially)?
    Study the docs : Optimization Guidelines.
    But Correctly designed runstats-profiles may be easier to manage over time, while not guaranteeing a particular result in every case.

  6. #6
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    Now I'm curious. How can I do that (on LUW especially)?
    As Mor just mentioned or make it real easy on yourself in your test environment drop the index in question.
    Dave

Tags for this Thread

Posting Permissions

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