My Oracle Support Banner

Linguistic Indexes Not Used For LIKE :BIND Predicates (Doc ID 1451804.1)

Last updated on OCTOBER 13, 2023

Applies to:

Gen 1 Exadata Cloud at Customer (Oracle Exadata Database Cloud Machine) - Version N/A and later
Oracle Cloud Infrastructure - Database Service - Version N/A and later
Oracle Database Backup Service - Version N/A and later
Oracle Database Cloud Exadata Service - Version N/A and later
Oracle Database Cloud Service - Version N/A and later
Information in this document applies to any platform.


A query using LIKE 'CONSTANT%' with nls_comp=LINGUISTIC against a column with a linguistic index defined on it.  This produces an efficient plan that uses a range scan on the index, but if a bind variable with the same value is used instead (LIKE :BIND, where :BIND = 'CONSTANT%') The query plan will not use a range scan, resulting in poor performance.  Hinting, if tried, does not help.

This will also happen when cursor_sharing=FORCE, which effectively replaces the literal with a bind value:

The last select statement can use the index t_col_ling if cursor_sharing=EXACT, but will uses a full table scan when cursor_sharing=FORCE.


Literal values were replaced by bind variables, the cursor_sharing parameter was changed from EXACT, or the use of linguistic indexes and nls_comp was introduced.


To view full details, sign in with your My Oracle Support account.

Don't have a My Oracle Support account? Click to get started!

In this Document

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.