Support NEAR Operator Within MNOT Operator (Doc ID 1326968.1)

Last updated on FEBRUARY 08, 2017

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


Support NEAR Operator Within MNOT Operator

In summary we want this to be possible:

select * from tab1 where contains(col1, 'word1 mnot near((word1,word2),3)') > 0;

We want values with word1 but not when it is close to word2, in a single "contains"

Given the following test case, we would like to return row 3 and eliminate row 2.

drop table tab1 purge;
create table tab1(id number primary key, col1 varchar2(300));

insert into tab1 values(1, 'a string with word1 only');
insert into tab1 values(2, 'a string with word1 word2 and word3 close together');
insert into tab1 values(3, 'a string with word1 word2 and word3 close together and out here word1 by itself');
insert into tab1 values(4, 'none of the words');
commit;

create index tab1_idx on tab1(col1) indextype is ctxsys.context;


col id format 99
col col1 format a75
select * from tab1 where contains(col1, 'word1 not near((word1,word2),1,true)') > 0;

ID COL1
---------------------------------------------------------------------------
1 a string with word1 only

Not useful as we need to return row 3.

select * from tab1 where contains(col1, 'word1 mnot word1 word2') > 0;

ID COL1
---------------------------------------------------------------------------
1 a string with word1 only
3 a string with word1 word2 and word3 close together and out here word1 by itself


Somehow useful as we return row 3

insert into tab1 values(5, 'a string with word1 noise1 word2 and word3 close together');
commit;
drop index tab1_idx;
create index tab1_idx on tab1(col1) indextype is ctxsys.context;

select * from tab1 where contains(col1, 'word1 mnot word1 word2') > 0;

ID COL1
---------------------------------------------------------------------------
1 a string with word1 only
3 a string with word1 word2 and word3 close together and out here word1 by itself
5 a string with word1 noise1 word2 and word3 close together


We would like that row 5 did not appear in the ouput as the words "word1" and "word2" are still close to each other .

If this query worked, it will fulfill the requirement:

select * from tab1 where contains(col1, 'word1 mnot near((word1,word2),3)') > 0;

We also want the NEAR operator to work here because the word1, word2 may
become word1, word2, word3 which are also handled by the NEAR operator.

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