$P Table is Not Being Used for Leading Single Wildcard Search Queries, Affecting Performance (Doc ID 1370538.1)

Last updated on NOVEMBER 09, 2011

Applies to:

Oracle Text - Version: 11.1.0.7 and later   [Release: 11.1 and later ]
Information in this document applies to any platform.

Symptoms

A search with a leading underscore wildcard like "_R" is not optimized by neither the substring nor prefix indexing attributes and following statement is seen in sql trace file.

SELECT/*+DYNAMIC_SAMPLING(0) INDEX(T "DR$NK$X")*/ DISTINCT TOKEN_TEXT
FROM "H"."DR$NK$I" T WHERE TOKEN_TEXT LIKE :lkexpr and
(TOKEN_TYPE = 0 OR TOKEN_TYPE = 4 OR TOKEN_TYPE BETWEEN 16 AND 74)

***value of :lkexpr = "_R"

This has an impact on the performance of such queries.

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