Linguistic Indexes Not Used For LIKE :BIND Predicates
Last updated on SEPTEMBER 28, 2016
Applies to:Oracle Database - Enterprise Edition - Version 188.8.131.52 and later
Oracle Database - Standard Edition - Version 184.108.40.206 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.
Sign In with your My Oracle Support account
Don't have a My Oracle Support account? Click to get started
My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms