Results 1 to 5 of 5
  1. #1
    Join Date
    Oct 2002
    Posts
    10

    Unanswered: Index not being used

    We are running a query with a where in () clause.

    If the in clause has 10 values, the index is used, if it has 150, the index is not used. This is on a table with 1M+ rows....

    Why is the optimizer choosing not to use the index for such a low number of rows?


    Any help appreciated!

  2. #2
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650

    Re: Index not being used

    If there are a large of number of comparisions to be done, db2 may end up reading all pages(or atleast a majority of them) from the table and hence it makes little sense for it to access the index first and then the table ..

    You may consider using more options in your RUNSTATS (like DISTRIBUTION, DETAILED etc) if you are not doing already ...

    Also, consider the impact of changing the NUM_QUANTILES and NUM_FREQVALUES parameters ...

    Cheers
    Sathyaram

    Originally posted by Ae589
    We are running a query with a where in () clause.

    If the in clause has 10 values, the index is used, if it has 150, the index is not used. This is on a table with 1M+ rows....

    Why is the optimizer choosing not to use the index for such a low number of rows?


    Any help appreciated!
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  3. #3
    Join Date
    Jun 2003
    Location
    Canada
    Posts
    35

    might want to try a workaround ....

    Once you tried the runstats with detailled option (as mentionned in previous reply). If the optimizer still does want to use the index ... you could try to put your 150+ IN clause in a table with a Unique index (followed by a runstats) and replace the IN by an inner join with that new table ....

  4. #4
    Join Date
    Oct 2002
    Posts
    10
    Thanks guys.

    I believe runstats detailed is already being run nightly.

    The IN list is created dynamically, so it would have to be a temp table. Not sure anout the performance of that.

  5. #5
    Join Date
    May 2002
    Location
    santa ana
    Posts
    6

    Re: Index not being used

    db2advis tool is very good to identify if you should add additional or removal indexes for your query.

    db2advis -h to get a full syntax.

Posting Permissions

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