Getting DB2 to use multiple indexes for OR WHERE clause?

loverde · #1 (**permalink**) 04-07-04, 12:10

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?

n_i · #2 (**permalink**) 04-07-04, 14:57

Quote:

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.

loverde · #3 (**permalink**) 04-07-04, 15:45

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.....

Marcus_A · #4 (**permalink**) 04-07-04, 15:58

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.

loverde · #5 (**permalink**) 04-07-04, 18:25

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.

Marcus_A · #6 (**permalink**) 04-07-04, 18:34

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.