Skip to Main Content

Token indexed, but CONTAINS cannot retrieve it

DannyS-OracleFeb 28 2018 — edited Feb 28 2018

Hi everyone,

I am building a CONTEXT index for my Apex app, with a prinjoin character '-' (hyphen) added within it. After building the index, the DR$INDEX_NAME$I table has successfully indexed all tokens containing '-', for example 'WEB-SITE', 'PSEUDO-SCIENCE'; but whenever I searched the tokens with the CONTAINS, my query result returns nothing. Any suggestions on how I can debug or fix the index?

select count(*)

from   MDT_BASE

where  contains(MDT_OTSEARCH_COLS, 'web-site')>0

The above search returns 'no data found.'

Since I can not share the sensitive table data, here is the description of the index:

CEMS_INDEX_SEARCH

=== INDEX DESCRIPTION ===

name: "CHACE"."CEMS_INDEX_SEARCH" index id: 69035 index type: context base table: "CHACE"."MDT_BASE" primary key column: MDT_INDEX text column: MDT_OTSEARCH_COLS text column type: VARCHAR2(1) language column: format column: charset column: configuration column: Query Stats Enabled: NO status: INDEXED full optimize token: full optimize count: docid count: 17851 nextid: 17852

=== INDEX OBJECTS ===

datastore: MULTI_COLUMN_DATASTORE columns: FISCAL_QUARTER_NAME, APPROVAL_ID, APPROVAL_VERSION, END_USER_NAME, APPROVAL_ITEM, DISCUSSION_BY, DEAL_STATUS, REGION, Q_DEAL_ID, APPROVAL_ITEM_ACTION, TAGS, DAS_TAGS filter: N,N,N,N,N,N,N,N,N,N,N,N filter: NULL_FILTER section group: BASIC_SECTION_GROUP field section: SECTION_TAGS section tag: TAGS visible: Y field id: 16 lexer: BASIC_LEXER printjoins: - startjoins: # mixed_case: NO index_themes: YES index_stems: ENGLISH wordlist: BASIC_WORDLIST stemmer: ENGLISH fuzzy_match: GENERIC storage: BASIC_STORAGE r_table_clause: lob (data) store as (cache) i_index_clause: compress 2

I cut the portion of BASIC_STOPLIST because it is too long.

Please let me know if you have any suggestions or questions. I am using Oracle DB v12.

This post has been answered by Roger Ford-Oracle on Feb 28 2018
Jump to Answer
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked due to inactivity on Mar 28 2018
Added on Feb 28 2018
3 comments
156 views