Using LIKE to Suppress an Index in Oracle 11gR2

By: Richard Niemiec


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.

 

0105_002

 

 

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

 

0105_003

 

 

 

 

 

 

 

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

 

0105_004

 

0106_001

 

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.

 

 

Leave a Reply