Results 1 to 8 of 8
  1. #1
    Join Date
    Sep 2002
    Posts
    456

    Unanswered: Runstats - Does not help

    Hi, I have a query like this:

    select min(col1)
    from tab
    where somedate between 'date1' and 'date2'

    Now, I have one index on column col1 and another one on somedate column. When I do runstats (I tried six different combinations) the query execution time goes up (more than couple of minutes) and access plan revealed it's using the index with col1.

    I have also kept old statistics of production (a year back) and when I apply that script and then run the query again it works fine (less than one second). This time access plan revealed that it's making using of index on column somedate!!!

    So, does the notion that DB2 optimizer will pick the best possible path when provided with updated statistics is wrong? Or there are some flaws in the RUNSTATS or Optimizer code?

    Your comments are appreciated.

    dollar

  2. #2
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    This sounds like a familiar question posted recently?

    What is the clustering index or the index used for reorgs. Maybe that has changed since when you had good response time? If somedate is the clustering index, it is more likely to pick that one for access. Make sure the table is reorged, then do runstats again.

    You could also try different query optimization levels (higher or lower).

    Lastly, I would check for APAR's in FP4 or APAR's yet to be fixed.
    http://www-3.ibm.com/cgi-bin/db2www/...lib.d2w/report
    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
    I would say, the best index for that query is:

    (someday, col1)

    Then you will get an index only access.

  4. #4
    Join Date
    Mar 2003
    Posts
    343
    I didn't think a between could be resolved with an index only access though.

  5. #5
    Join Date
    Sep 2002
    Posts
    456
    Well, the query is making use of index but not the right one. When it makes use of index with time column (one in where clause), the result is instant but when it's using the index with ID column (one in select clause), it takes over 10 minutes.


    dollar


    Originally posted by cchattoraj

    I didn't think a between could be resolved with an index only access though.

  6. #6
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    A between can use index only access, but in this case there is the min function on the selected column which is not in the index. So index only access is not possible.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  7. #7
    Join Date
    Mar 2003
    Posts
    343
    You're right - I meant min and wrote between. Thank you.

  8. #8
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    Dollar you should also read the other post on indexes

    http://www.dbforums.com/t979949.html

    Cheers
    Sathyaram
    Visit the new-look IDUG Website , register to gain access to the excellent content.

Posting Permissions

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