Using LIKE to Suppress an Index in Oracle 11gR2

By Richard Niemiec on April 19, 2013

LIKE, in some cases, uses an index, while in others, it does not. The most common uses of LIKE are LIKE ‘%somevalue%’ or LIKE ‘somevalue%’ (where the % is only at the end of the search string). Only one of these cases uses the index—the case where the value is first, LIKE ‘somevalue%’.

Let’s examine this using a set of examples. First, create an index on the SCOTT.EMP table for the ENAME column so you can use an index when looking up employee names. This allows you to see when the index is used with a LIKE and when it is not.





Now let’s examine what happens when using LIKE with ‘%somevalue%’:










Now let’s put the value first, before the ‘%’:






Notice that when the ‘%’ appears first, the index is not used, but when you put the value first, Oracle is able to utilize the index.



Related Posts

Leave a Reply