| Tuning 'LIKE' Clause [message #336688] |
Mon, 28 July 2008 12:48  |
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  |
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 |
 |
Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...
|
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
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
