Linguistic Indexes Not Used For LIKE :BIND Predicates
(Doc ID 1451804.1)
Last updated on MAY 19, 2020
Applies to:Oracle Database - Enterprise Edition - Version 220.127.116.11 and later
Oracle Database - Standard Edition - Version 18.104.22.168 and later
Oracle Database Cloud Schema Service - Version N/A and later
Oracle Database Exadata Express Cloud Service - Version N/A and later
Oracle Database Exadata Cloud Machine - 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