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.