Tuning 'LIKE' Clause [message #336688] |
Mon, 28 July 2008 12:48 ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
getritesh
Messages: 235 Registered: July 2006 Location: INDIA
|
Senior Member |
![GETRITESH00%40YAHOO.COM](/forum/theme/orafaq/images/yahoo.png)
|
|
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 message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
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 Go to previous message](/forum/theme/orafaq/images/up.png) |
![](http://www.orafaq.com/forum/images/custom_avatars/102589.gif) |
Michel Cadot
Messages: 68678 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
|
|
|