Results 1 to 9 of 9
  1. #1
    Join Date
    Apr 2004
    Posts
    3

    Unanswered: Getting DB2 to use multiple indexes for OR WHERE clause?

    Is there a way to get DB2 to use multiple indexes on an OR where clause?

    Basically, I have a table that supports two columns, one with a value input by the user, and another with an alternate value determined by application specific means. Something like:

    create table SAMPLE_TABLE (
    VALUE_COL VARCHAR(64),
    ALT_VALUE_COL VARCHAR(64)
    );

    And I want to do a query to find all rows with either the value or alternate value are equal to 'FOO'. That is:

    SELECT * FROM SAMPLE_TABLE
    WHERE VALUE_COL = 'FOO' OR ALT_VALUE_COL = 'FOO'

    In DB2, there are two indexes on the table, one on each column. The problem is that DB2 in its ultimate wisdom refuses to use the indexes. Instead it opts to do a full table scan (DB2 V7 on the mainframe) according to explain.

    If I change the query to:

    SELECT * FROM SAMPLE_TABLE
    WHERE VALUE_COL = 'FOO'
    UNION
    SELECT * FROM SAMPLE_TABLE
    WHERE ALT_VALUE_COL = 'FOO'

    Then DB2 does in fact use both indexes to perform the query.

    But I don't want to have to specify the query as a union because it will cause some problems with the O/R mapping tool that I am using.

    Is it possible to restructure the query or specify a different type of index to force DB2 to use both indexes instead of doing a full table scan?

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1

    Re: Getting DB2 to use multiple indexes for OR WHERE clause?

    Originally posted by loverde
    Is there a way to get DB2 to use multiple indexes on an OR where clause?

    Basically, I have a table that supports two columns, one with a value input by the user, and another with an alternate value determined by application specific means. Something like:

    create table SAMPLE_TABLE (
    VALUE_COL VARCHAR(64),
    ALT_VALUE_COL VARCHAR(64)
    );

    And I want to do a query to find all rows with either the value or alternate value are equal to 'FOO'. That is:

    SELECT * FROM SAMPLE_TABLE
    WHERE VALUE_COL = 'FOO' OR ALT_VALUE_COL = 'FOO'

    In DB2, there are two indexes on the table, one on each column. The problem is that DB2 in its ultimate wisdom refuses to use the indexes. Instead it opts to do a full table scan (DB2 V7 on the mainframe) according to explain.

    If I change the query to:

    SELECT * FROM SAMPLE_TABLE
    WHERE VALUE_COL = 'FOO'
    UNION
    SELECT * FROM SAMPLE_TABLE
    WHERE ALT_VALUE_COL = 'FOO'

    Then DB2 does in fact use both indexes to perform the query.

    But I don't want to have to specify the query as a union because it will cause some problems with the O/R mapping tool that I am using.

    Is it possible to restructure the query or specify a different type of index to force DB2 to use both indexes instead of doing a full table scan?
    I guess the best you could do without changing your data design is to include ALT_VALUE_COL into the index on VALUE_COL - it may invoke index scan instead of table scan. However, it will still be the entire index.

  3. #3
    Join Date
    Apr 2004
    Posts
    3
    We tried multicolumn indexes in both directions.

    VALUE_COL and ALT_VALUE_COL
    ALT_VALUE_COL and VALUE_COL

    But DB2 is still determined to opt for a full table scan unless the query is structured as a union.

    Still longing for DB2 to support hints.....

  4. #4
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    I would make certain that you have executed runstats with the full amount of stats available on the table and indexes in question.

    Also keep in mind that if the number of rows in the table is low, or the fullkeycard is low (lots of duplicate keys), DB2 will find it more efficient to a tablespace scan. If the distribution of values is skewed, make sure you collect KEYCARD and FREQVAL for the top n occurrences.

    If you have host variables, make sure it is defined as the same data type and length as the DB2 column.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  5. #5
    Join Date
    Apr 2004
    Posts
    3
    Thanks for the advice. I should have stated this in the original post:

    We ran runstats after creating the indexes. The table has approximately 500,000 rows and is expected to grow to around 4 million ones it reaches saturation in production (we are expanding the amount of history being stored).

    The union form of the query runs for about .25 seconds and the OR version runs for about 20 seconds.

    As you would expect from those numbers, explain is indicating that the indexes are being used for the union query but not for the OR query.

    It the ALT_VALUE_COL does have a high occurrence of null values, so that might be why it is electing to go with a full table scan.

    Here are the SYSIBM.SYSINDEXES values for the VALUE_COL and ALT_VALUE_COL indexes:

    Code:
    NAME          FIRSTKEYCARD FULLKEYCARD NLEAF NLEVELS
    FCX01708   396072             396072           3351   3
    FCX01709   1                        1                       947     3
    I apologize, my DB2 optimization knowledge is limited and my DBA has not been helpful on this problem, so I'm at a bit of a loss.

    Any help is greatly appreciated.

  6. #6
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Those stats show that index FCX01709 has only one value, so DB2 would not normally use that index.

    As I mentioned above, if the distribution of values is skewed, make sure you collect KEYCARD and FREQVAL for the top n occurrences when doing the runstats. A generic runstats is not sufficient in these cases.

    If that does not help, I would search for APAR's that might address the problem with a PTF.

    Also, I suspect you are not giving the exact DDL and DML, so there may be some detail that is not obvious here.
    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
    Jan 2004
    Location
    Europe, Finland, Helsinki
    Posts
    64
    Why not UNION?

    You can now have in v7 UNION in a view. And having a VIEW shoud not be any problem for you?

    Cheers, Bill

  8. #8
    Join Date
    Nov 2003
    Location
    Germany
    Posts
    62
    Did you try your first SQL with the clause: OPTIMIZE FOR 1 ROW?

  9. #9
    Join Date
    Jul 2003
    Posts
    6
    You could have used a BIND OPTHINT to force the optimizer, had this been a batch program.

Posting Permissions

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