Tip: queries with LIKE are too slow when using Agile Platform and Oracle 10g

Tip: queries with LIKE are too slow when using Agile Platform and Oracle 10g

  
Symptom

You have a query that looks up a text value in a entity with a lot of entries, and you notice that the query is very slow.
Your text value is smaller than 2000 characters (ergo created as VARCHAR2 in the database).
Your query uses a like operator.
The Agile Platform is installed in a Oracle 10g database, and has case-insensitive behavior (first installed with 4.2.4.13 or above).

For example, the following query is very slow:



However, you notice that the very similar query below (replacing like with =) behaves much faster:




Cause

The Agile Platform uses function indexes based on NLSSORT BINARY_AI when installed as case-insensitive. In Oracle 10gR2, function indexes can be used in all operations - however, they are ignored when the query uses like. The ability to use function-indexes with the like clause was introduced by Oracle in 11gR1only (see document Linguistic Sorting - Frequently Asked Questions [ID 227335.1] in Oracle Support Portal).
Below is a screenshot from the relevant part of the document:



Resolution

If are being affected by this problem, you should consider replacing the like clause with = . This will not be a solution however if you need to do searches (using %) - in which case the solution will have to involve upgrading Oracle to 11gR2.
Also, in very specific situations, you might be able to get a workaround using any of the other comparison operators to achieve a result similar to like - but beware that it might turn your code into something unreadable.

If you have anything to add to this post, please feel free to reply!
Cheers,

References:
http://www.myoracleguide.com/xl/Linguistic_Sorting_Frequently_Asked_Questions.htm