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

Back to Forums

AcacioPN

Staff

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

Discussion

OS Certified

Performance

SQL

How-to

Oracle

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:
https://www.myoracleguide.com/xl/Linguistic_Sorting_Frequently_Asked_Questions.htm

22 Mar 2012

Community GuidelinesBe kind and respectful, give credit to the original source of content, and search for duplicates before posting.

See the full guidelines