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

    Unanswered: Problem with indexes

    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 INDEX1 over the exact match of INDEX2 .
    Now, here's what makes it even stranger, this is only happening at a couple of client sites, others are working as expected.
    Thanks

  2. #2
    Join Date
    Apr 2012
    Posts
    1,034
    Provided Answers: 18
    Most likely: different runstats. The selectivity of acc_num_yr (and possibly other columns) might be poor. If it's a high-frequency query, consider column-group-stats, or statistical view.

  3. #3
    Join Date
    Sep 2014
    Location
    Long Island, New York
    Posts
    14
    Thanks for the quick response db2mor. I did perform a RUNSTATS on the table and indexes. I also rebound the packages. Can you explain your answer, I didn't really follow. Thanks.

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
  •