IBM Mainframe Forum

Hi All,
I need to fine tune the below query fro better performance, please help.

Select FNAME, MNAME, SURNAME, DOB, ADDRESS, PHONE from INDIVIDUAL_DATA
WHERE DOB = V_DOB
AND (SURNAME = V_SURNAME
OR (SURNAME LIKE '%' || ' ' || V_SURNAME)
OR (SURNAME LIKE V_SURNAME || ' ' || '%')
OR (SURNAME LIKE '%' || ' ' ||
V_SURNAME || ' ' || '%'));

I have an index on DOB and SURNAME.
I have a huge amount of data.
please suggest ways to fine tune it. or any alternate to pattern match using LIKE predicate.

Please use the code tags to present your query and make it more readable.
Perhaps an index on:

Pardon Nicc, not getting your point.
V_Surname is the variable which will have the surname passed to this query.

If V_SURNAME is a host variable then it should be pre-pended with ':'. Nowhere in your post is that done. If you want help then post accurately instead of wasting everyone's time. As a programmer you need to take care of the details ALL the time - not just when coding your program.

Nicc, I didn't said these are host variables. this code snippet is the part of an stored procedure. and V_SURNAME is the stored procedure local variable.

You also did not say that it was a stored procedure. How are we supposed to know. Please give full details next time.

will never be able to do an index scan, only a full table scan. In other words, your design is brown, the same colour as the stuff that hits the fan!