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 laterOracle 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.
Symptoms
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.
Changes
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.
Cause
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
| Symptoms |
| Changes |
| Cause |
| Solution |
| References |