Home » Server Options » Text & interMedia » Near Clause (Oracle 11g)
Near Clause [message #496270] Fri, 25 February 2011 11:40 Go to next message
rajivn786
Messages: 136
Registered: January 2010
Senior Member
Hi,

I have an issue regarding near clause..Created sample table and also created an index including stop list (indextype
is ctxsys.context parameters ('stoplist ctxsys.empty_stoplist')..

Table create & insert are as follows :

create table comic (mus varchar2(20), rep_text varchar2(1000));
   Insert into comic values ('1','press on a door');
   Insert into comic values ('2','press on a remit');
   Insert into comic values ('3','press on a recover');
   Insert into comic values ('4','press on a resolve');
   Insert into comic values ('5','press in remis');
   
   
       create  index a_idx on comic (rep_text) indextype
        is 
        ctxsys.context parameters ('stoplist ctxsys.empty_stoplist')
        parallel 32;


The following code works good..
  Select distinct mus from comic
   where contains(rep_text,'near((press,remit = recover = resolve  ), 3)') > 0


But now when I add the following it is giving incorrect results..

Select distinct mus from comic
   where contains(rep_text,'near((press,remit = recover = resolve = in remis ), 3)') > 0



In fact the code works fine, but it doesn't equal the count when I perform the same with union..
Select distinct mus from comic   where contains(rep_text,'near((press,remit), 3)') > 0
union
Select distinct mus from comic   where contains(rep_text,'near((press,recover), 3)') > 0
union
Select distinct mus from comic   where contains(rep_text,'near((press,resolve), 3)') > 0
union
Select distinct mus from comic   where contains(rep_text,'near((press,in remis), 3)') > 0


May I know what's the problem?? I think it's with the in-clause...Can anyone resolve this??

Re: Near Clause [message #496276 is a reply to message #496270] Fri, 25 February 2011 12:42 Go to previous messageGo to next message
Barbara Boehmer
Messages: 7937
Registered: November 2002
Location: California, USA
Senior Member
Although you have used ctxsys.empty_stoplist, so that the word "in" is tokenized and indexed, the word "in" is still a reserved word. In order to search for "in", you have to enclose it within curly brackets to escape it like {in}. However, you have another problem that the equivalence operator "=" for the near operator can only be used on a single word and "in remis" is two words, not one.
Re: Near Clause [message #496277 is a reply to message #496276] Fri, 25 February 2011 12:52 Go to previous messageGo to next message
rajivn786
Messages: 136
Registered: January 2010
Senior Member
Yes Barbara,

I did face this issue several times...Is there any workaround for searching multiple terms using nearby clause...So is is that whenever we encounter multiple terms in near clause do we need to do them separately using union statement?

Thanks..
Re: Near Clause [message #496278 is a reply to message #496277] Fri, 25 February 2011 13:10 Go to previous message
Barbara Boehmer
Messages: 7937
Registered: November 2002
Location: California, USA
Senior Member
The following would be more efficient syntax.

SCOTT@orcl_11gR2> create table comic
  2    (mus	  varchar2 (  20),
  3  	rep_text  varchar2 (1000))
  4  /

Table created.

SCOTT@orcl_11gR2> Insert all
  2  into comic values ('1', 'press on a door')
  3  into comic values ('2', 'press on a remit')
  4  into comic values ('3', 'press on a recover')
  5  into comic values ('4', 'press on a resolve')
  6  into comic values ('5', 'press in remis')
  7  select * from dual
  8  /

5 rows created.

SCOTT@orcl_11gR2> create index a_idx on comic (rep_text)
  2  indextype is ctxsys.context
  3  parameters ('stoplist ctxsys.empty_stoplist')
  4  parallel 32
  5  /

Index created.

SCOTT@orcl_11gR2> Select distinct mus from comic
  2  where  contains
  3  	      (rep_text,
  4  	       'near ((press, remit = recover = resolve), 3) or
  5  		near ((press, {in} remis), 3)') > 0
  6  /

MUS
--------------------
3
5
2
4

4 rows selected.

SCOTT@orcl_11gR2>

Previous Topic: Re: Contains Clause is not working on certain scenario (split from unrelated hijacked thread by bb)
Next Topic: Oracle Text Index using Soundex
Goto Forum:
  


Current Time: Sat Jul 26 12:37:43 CDT 2014

Total time taken to generate the page: 0.15006 seconds