Home » RDBMS Server » Performance Tuning » Tuning 'LIKE' Clause (Oracle,10g,Windows XP)
Tuning 'LIKE' Clause [message #336688] Mon, 28 July 2008 12:48 Go to next message
getritesh
Messages: 235
Registered: July 2006
Location: INDIA
Senior Member

Please give me tips in tuning SQL statement invoving 'LIKE-clause' in Where clause.

Thanks in advance.
Re: Tuning 'LIKE' Clause [message #336702 is a reply to message #336688] Mon, 28 July 2008 13:59 Go to previous messageGo to next message
sunil_v_mishra
Messages: 506
Registered: March 2005
Senior Member
Use the LIKE operator to check if a string value matches a given pattern. If a value matches a pattern, the operator returns True; otherwise, it returns False.

In a pattern, use the underscore character (_) to match any single character. Use the percent sign (%) to match any number of characters including zero. Use the backslash character and the underscore character (\_) to match the underscore character, use the backslash character and the percent sign (\%) to match the percent sign, use two backslash characters (\\) to match the backslash character. Use any other character, like a letter or a digit, to match itself.

The LIKE operator is case-sensitive. To match values without case, convert both the string and the pattern to either upper or lower case with UCase or LCase.

If either the checked value or the pattern is NULL, the LIKE operator also returns NULL.

Don't use Like operator for accessing any thing from large table.

Index will not be used to access table when u will use like operator. So there will always be FTS (full table scan)

Re: Tuning 'LIKE' Clause [message #336709 is a reply to message #336702] Mon, 28 July 2008 14:24 Go to previous message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Index will not be used to access table when u will use like operator. So there will always be FTS (full table scan)

This is wrong. Oracle can use index if the leading characters are fixed (for instance, like 'foo%').

Regards
Michel
Previous Topic: type tables in plsql/ variable IN list
Next Topic: gather stats
Goto Forum:
  


Current Time: Fri Dec 02 18:24:52 CST 2016

Total time taken to generate the page: 0.21912 seconds