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

Last updated on SEPTEMBER 28, 2016

Applies to:

Oracle Database - Enterprise Edition - Version 11.1.0.6 and later
Oracle Database - Standard Edition - Version 11.1.0.6 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

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