Wildcard characters

Oracle and DB2® Servers use the underscore character ("_") as a single character wildcard and the percent character ("%") as a wildcard character that can match zero or more characters. If possible, you should avoid using these two characters in indexed fields. Take for example the case where you have a record with ORDER_NO equal to E1_DIV01_03215466.

The following query is fast because only records with 'E1_DIV01_03215466' qualifies:

      select order_header_key
      from yfs_order_header
      where order_no = 'E1_DIV01_03215466';
   

But the following query can be very slow, especially if you have millions of records that start with "E1%":

      select order_header_key
      from yfs_order_header
      where order_no like 'E1_DIV01_0321546%';
   

In the example above, records with ORDER_NO equal to E11DIV0110321546, E11DIV01A0321546 and so on qualifies. As a result, the database server has to find every qualifying record with ORDER_NOs ranging from E1<low value>DIV01<low value>0321546% to E1<high value>DIV01<high value>0321546%.

If you use wildcards as part of the column value, you can escape the wildcards as shown in the following example:

      select order_header_key
      from yfs_order_header
      where order_no like 'E1\_DIV01\_0321546%' 
      escape '\';