Results 1 to 4 of 4
  1. #1
    Join Date
    Aug 2002
    Posts
    76

    Unanswered: strange table scan rather than index read

    We have a fairly large table (96 million rows) and have always had 2 main indexes, the primary key and an index on "STATUS" field.

    We recently added a new index on an ACCOUNT_NUMBER type field, and now our query that just has WHERE STATUS = ? does full table scans rather than using its index.

    The only way we get it back to doing index reads is dropping the unrelated ACCOUNT_NUMBER index.

    We obviously run runstats after each index creation or drop but we get these same results, the only thing that gets it back to using the index is to get rid of the 3rd index.

    We can re-create this in the same table in a few different environments including 8.2 fix 14 and 9.2 fix 3, all on solaris.

    Anyone else seen this?

  2. #2
    Join Date
    Sep 2003
    Posts
    237
    What is the cardinality(how many distinct values) of STATUS field? If it is low(like 3 or 4) you are better off doing table scan.
    mota

  3. #3
    Join Date
    Aug 2002
    Posts
    76
    Yes, I would assume this probably does have something to do with the cardinality. There are probably 10 different values of the Status field.

    When we query on status we are looking for status = 1

    At any given time there are from 1000 to 2 million of status 1 and the rest are other status values.

    I know we are not better off doing a full scan. When just the 2 indexes are present, we use the Status index and the query runs in .5 to 2 second timeframes.

    When the 3rd index is created, that has nothing to do with the Status field, it goes to full table scans and same query runs >15 minutes.

    I will probably open ticket with IBM, but very strange that the presence of the unrelated index changes the access plan on the STATUS query.

  4. #4
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    You need to post your runstats command. I would recommend this:

    runstats on table table-name with disitribution on key columns with detailed indexes all
    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
  •