Struggling with SQL query's performance in DB2. Please Help!!!

[levka]

Correct index utilization in SQL queries on DB2

Hi,

I am building an underlying query that ought to be able to locate the unique record on the PERSON table which consists of the following columns: EID, SSN, LAST_NAME, FIRST_NAME, DOB

I am utilizing the "3 out of 4" technique which means that combination of any 3 out of 4 incoming parameters should be able to uniquely identify a record. In other words, the query below should produce 0 ot 1 record which is the expected outcome. The table has 5 indexes:
* a compound index on SSN, LAST_NAME, FIRST_NAME, DOB
* separate indexes on each of the columns above.

I need to be able to optimize the following query (or possibly, re-write it) to prevent or minimize the table scans:

SELECT EID 
FROM myTABLE WHERE 
( rtrim(SSN) = '010101010' AND soundex(LAST_NAME) = soundex('Doe') AND soundex(FIRST_NAME) = soundex('John' )) 
or ( rtrim(SSN) = '010101010' AND  DOB = cast('09/10/1981' as date) AND soundex(LAST_NAME) = soundex('Doe' )) 
or ( rtrim(SSN) = '010101010' AND  DOB = cast('09/10/1981' as date) AND soundex(FIRST_NAME) = soundex('John' )) 
or ( DOB = cast('09/10/1981' as date) AND soundex(LAST_NAME) = soundex('Doe') AND soundex(FIRST_NAME) = soundex('John' ));

This query performs table scan that I must avoid in order to get the best results. This query is to be used in as a base for Web Service, hence the performance is essential.

Any suggestions or help would be greatly appreciated.

Best regards,

levka

 

[MarcLodge]

Hi Levka,
I could be wrong, but I thought that the soundex function was one of those that never used an index and always resulted in a tablespace scan. It would probably be worthwhile running EXPLAIN against the query so as to give you a much better idea of what indexes etc. are being used.
Marc

 

[gregsimpson]

I have had great success doing similar queries where I don't always know how many of the predicates will be input using between with low values and high values for the predicates not supplied. I attempted to explain it recently in the following link.

thread178-472339 also recommend that you change your index so that it includes the eid. This means that DB2 can do an index only scan(as everything in the query is in the index) rather than having to go to the table to retrieve the eid. This should make a difference to io's on all rows which match. It's a technique called index overloading and will speed up your inquiry greatly.

Using the link above you can use ANDs rather than OR's which are much more efficient.

Cheers
Greg

 

[gregsimpson]

I should perhaps have mentione that adding

optimize for 1 row can also often help avoid table scans.

Also try

Fetch First 1 row only

although that can be very quirky and often relies on fixpacks to work as IBM say it will. Without the fixpacks it tablescans in situations when it shouldn't.

Greg