Correct index utilization in SQL queries on DB2

[levka]

Hi,

My task is to locate the unique record on the table which contains of the following columns: EID, SSN, LAST_NAME, FIRST_NAME, DOB

The technique that I am planning to use is called "3 out of 4" which means that combination of any 3 out of 4 incoming parameters should be able to uniquely identify a record. In other words, the following query may produce 0, 1 or more records. The condition that satisfies me is when 0 or 1 records are produced. The table has a compound index on SSN, LAST_NAME, FIRST_NAME, DOB as well as separate index on each of the columns above (total of 5 indexes). 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' ));

Any suggestions would be very much appreciated.

Best regards,

Leo

 

[tlbroadbent]

Remove the rtrim from the SSN column. It shouldn't be necessary and having a function on the column prevents the use of an index on that column. For more optimization hints, you should post your question in the forum for the database you use. If you want to get the best answer for your question read faq183-874 and thread183-468158.


Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm