Page 1 of 1

SELECT QAUERY FINE TUNE or Alternate of LIKE prdicate

PostPosted: Thu Nov 19, 2015 3:31 pm
by gauravfrankly
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.

Re: SELECT QAUERY FINE TUNE or Alternate of LIKE prdicate

PostPosted: Thu Nov 19, 2015 7:38 pm
by NicC
Please use the code tags to present your query and make it more readable.
Perhaps an index on:
SURNAME' 'V_SURNAME

Re: SELECT QAUERY FINE TUNE or Alternate of LIKE prdicate

PostPosted: Fri Nov 20, 2015 10:33 am
by gauravfrankly
Pardon Nicc, not getting your point.
V_Surname is the variable which will have the surname passed to this query.

Re: SELECT QAUERY FINE TUNE or Alternate of LIKE prdicate

PostPosted: Fri Nov 20, 2015 5:04 pm
by NicC
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.

Re: SELECT QAUERY FINE TUNE or Alternate of LIKE prdicate

PostPosted: Fri Nov 20, 2015 6:10 pm
by gauravfrankly
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.

Re: SELECT QAUERY FINE TUNE or Alternate of LIKE prdicate

PostPosted: Fri Nov 20, 2015 8:46 pm
by NicC
You also did not say that it was a stored procedure. How are we supposed to know. Please give full details next time.

Re: SELECT QAUERY FINE TUNE or Alternate of LIKE prdicate

PostPosted: Sun Nov 22, 2015 2:25 pm
by prino
SURNAME LIKE '%' || ' ' || V_SURNAME)

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!