Discussions
Categories
- 196.9K All Categories
- 2.2K Data
- 239 Big Data Appliance
- 1.9K Data Science
- 450.3K Databases
- 221.7K General Database Discussions
- 3.8K Java and JavaScript in the Database
- 31 Multilingual Engine
- 550 MySQL Community Space
- 478 NoSQL Database
- 7.9K Oracle Database Express Edition (XE)
- 3K ORDS, SODA & JSON in the Database
- 545 SQLcl
- 4K SQL Developer Data Modeler
- 187K SQL & PL/SQL
- 21.3K SQL Developer
- 295.9K Development
- 17 Developer Projects
- 138 Programming Languages
- 292.6K Development Tools
- 107 DevOps
- 3.1K QA/Testing
- 646K Java
- 28 Java Learning Subscription
- 37K Database Connectivity
- 155 Java Community Process
- 105 Java 25
- 22.1K Java APIs
- 138.1K Java Development Tools
- 165.3K Java EE (Java Enterprise Edition)
- 18 Java Essentials
- 160 Java 8 Questions
- 86K Java Programming
- 80 Java Puzzle Ball
- 65.1K New To Java
- 1.7K Training / Learning / Certification
- 13.8K Java HotSpot Virtual Machine
- 94.3K Java SE
- 13.8K Java Security
- 204 Java User Groups
- 24 JavaScript - Nashorn
- Programs
- 440 LiveLabs
- 38 Workshops
- 10.2K Software
- 6.7K Berkeley DB Family
- 3.5K JHeadstart
- 5.7K Other Languages
- 2.3K Chinese
- 171 Deutsche Oracle Community
- 1.1K Español
- 1.9K Japanese
- 232 Portuguese
Oracle Text CONTAINS in user_datastore returns wrong results for mutliple AND tokens

I have a User_datastore defined based on a procedure that collects data from various tables and indexes them. The column that contains the oracle text is pub_notification_mv.index_search_flag_publication.
The query:
select pub_notification_mv.NOTIFICATION_ID from pub_notification_mv where contains(pub_notification_mv.index_search_flag_publication,'%COMME% AND %LANGUAGEISEN%') > 0 ` returns 1 row => 10000005 instead of 0
select pub_notification_mv.NOTIFICATION_ID from pub_notification_mv where contains(pub_notification_mv.index_search_flag_publication,'%COMME% AND %LANGUAGEISFR%') > 0 ` returns 1 which is valid => 10000005
The select inside the procedure returns
NOTIFICATION_ID LANGUAGE DESCRIPTION
10000005 LANGUAGEISFR comme tu habbitue
10000005LANGUAGEISEN olympiacos pireaus product small chain
SELECT * FROM DR$FTS_INDEX_PUBLICATION$I WHERE dr$fts_index_publication$i.token_text LIKE '%10000005%' returns:
TOKEN_TEXT
BRAND10000004A12/00001/2110000005TESTTESTLANGUAGEISFRCOMME
BRAND10000004A12/00001/2110000005TESTTESTLANGUAGEISENOLYMPIACOS
I have tried different combinations with CONTAINS and AND. I also tried the Structured Query with CONTAINS but still does not work.
Thanks,
Answers
-
What does this return?
select pub_notification_mv.NOTIFICATION_ID from pub_notification_mv where contains(pub_notification_mv.index_search_flag_publication,'%COMME%) 0 and contains(pub_notification_mv.index_search_flag_publication, '%LANGUAGEISEN%') > 0
Perhaps the column data changed and the text index is stale. Have you tried refreshing (drop and rebuild)?
-
Thanks @Paulzip for taking time to look at this. It returns 100005. Both terms COMME and LANGUAGEISEN are indexed so this OK but their combination it is not COMME AND LANGUAGEISEN. The index is on a Materialized view (on their base table) but I do not think that this matters. I am using 12c. Maybe the Lexer needs to be updated =>
BEGIN
ctx_ddl.create_preference('FREE_TEXT_SEARCH_PREF_PUB', 'user_datastore');
ctx_ddl.set_attribute('FREE_TEXT_SEARCH_PREF_PUB', 'procedure', 'PR_FREE_TEXT_SEARCH_PUBL');
ctx_ddl.set_attribute('FREE_TEXT_SEARCH_PREF_PUB', 'output_type', 'CLOB');
END;
BEGIN
ctx_ddl.create_preference('WILDCARD_PREF','BASIC_WORDLIST');
ctx_ddl.set_attribute('WILDCARD_PREF','wildcard_maxterms',50000);
ctx_ddl.set_attribute('WILDCARD_PREF','SUBSTRING_INDEX','TRUE');
END;
BEGIN
ctx_ddl.create_preference('FREE_TEXT_SEARCH_LEXER_PUB','BASIC_LEXER');
ctx_ddl.set_attribute('FREE_TEXT_SEARCH_LEXER_PUB','BASE_LETTER','YES');
ctx_ddl.set_attribute('FREE_TEXT_SEARCH_LEXER_PUB','printjoins','/');
END;
CREATE INDEX fts_index_publication ON
pub_notification_mv (
index_search_flag_publication
)
INDEXTYPE IS ctxsys.context PARAMETERS ( 'LEXER FREE_TEXT_SEARCH_LEXER_PUB Wordlist WILDCARD_PREF datastore
FREE_TEXT_SEARCH_PREF_PUB sync (on commit)'
);
-
I dropped and rebuild the index many times.