RE: Most efficient SQL index to use when where clause contains some ORs -- JAVA400-L

Nigel,

A couple of things.  First, try the archives for Midrange-L.  This is the 
general discussion for the iSeries and SQL is talked about a lot.  This topic I 
know has been discussed more than once.  You should find some good information 
there.

If the archives don't help you out you might consider posting this to the 
Midrange-L list.  There a several folks there who are very knowledgeable about 
SQL.

Directly to the point of you question, have you tried running the statement 
without the parameter markers?  I would expect the optimizer to suggest some 
sort of an index.  This does depend on the size of the file being queried.  A 
problem I experience in my shop is that one of our development files I 
frequently access with SQL only contains 22 million records.  The same 
production file contains close to 500 million records.  As you can guess, the 
optimizer returns two very different access methods.  If this applies to you, 
running the query against the production file for analysis purposes might 
provide your answer.

If the optimizer is still no help, my suggestion would be to try an index on 
the order by clause.  I have had success using that as the basis for my 
indexing instead of the where clause.

HTH,

Rick

-----Original Message-----
From: java400-l-bounces@xxxxxxxxxxxx
[mailto:java400-l-bounces@xxxxxxxxxxxx]On Behalf Of NGay@xxxxxxxxxxxxx
Sent: Wednesday, September 01, 2004 1:30 PM
To: java400-l@xxxxxxxxxxxx
Subject: Most efficient SQL index to use when where clause contains some
ORs


My apologies if this isn't quite the right group to be asking this too,
however I couldn't find a group specifically relating to query/SQL
execution.

I have a query very similar to:
SELECT * FROM XYZ
WHERE A = ? AND (B = ? OR C = ? OR D = ?) ORDER BY E, F

What indexes should I create to make this run efficiently?  The query
optimizer doesn't make any suggestions.  I have a redbook about DB2/400
indexing strategies, but it doesn't mention anything about what to do when
you have ORs in the WHERE clause.

The best results I've had so far was by creating 4 seperate indexes sorted
by A, B, C and D.  This causes the query optimiser to create bitmaps using
the indexes over A, B, C and D, and then combine the 4 bitmaps to
efficiently pick out the correct records, which is great.  But it then
manually sorts the results which kills the performance.

I've tried all sorts of variatons, such as indexes sorted by:
A, B, E, F
A, C, E, F and
A, D, E, F

or

A, B
A, C
A, D
A, E, F

but it never chooses to use any of these indexes and in fact they just make
things worse.  Any suggestions anybody?

BTW, there are many unique values in each of A, B, C and D, so encoded
vector indexes aren't really appropriate.

Thanks in advance for any insight anybody can offer me on this!

Nigel Gay
Computer Patent Annuities.





********************************************************************************
The information in this message is confidential and may be legally
privileged. It is intended solely for the addressee; access to this
email by anyone else is unauthorised.

If you are not the intended recipient: (1) you are kindly requested
to return a copy of this message to the sender indicating that you
have received it in error, and to destroy the received copy; and (2)
any disclosure or distribution of this message, as well as any action
taken or omitted to be taken in reliance on its content, is prohibited
and may be unlawful.
********************************************************************************

--
This is the Java Programming on and around the iSeries / AS400 (JAVA400-L) 
mailing list
To post a message email: JAVA400-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/java400-l
or email: JAVA400-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/java400-l.