1

I have a multicolumn datastore indexed using Oracle Text, and I am running queries using Contains keyword.
To weight the different columns differently I proceed as follow.

If the user searches for "horrible", the query issued to oracle will look like this : 

WHERE CONTAINS(indexname,
   '((horrible WITHIN column1) * 3) 
    OR ((horrible WITHIN column2) * 2))') > 1

But to add a category filter that is also indexed, I do this : 

WHERE CONTAINS(indexname,
   '((horrible WITHIN Column1) * 3) 
    OR ((horrible WITHIN Column2) * 2))
    AND (movie WITHIN CategoryColumn)', 1) > 1

This filters by category, but that messes up completely the scoring, because Oracle text will take the lowest score from any side of the AND keyword. Instead I would like to instruct oracle to ignore the right side of my AND.

Is there a way to get this specific part of the query ignored by the scoring?

Basically, I want to score according to 

(horrible WITHIN Column1) * 3 
OR (horrible WITHIN Column2) * 2)

but I want to select according to 

'((horrible WITHIN Column1) * 3) 
 OR ((horrible WITHIN Column2) * 2))
 AND (movie WITHIN CategoryColumn)'
Stéphane
  • 11,755
  • 7
  • 49
  • 63

1 Answers1

1

There is a mention of

Specify how the score from child elements of OR and AND operators should be merged.

in Oracle Docs in the Alternative and User-defined Scoring secion, but not a lot of examples.

Using query relaxation might be simpler in this case (if it works), e.g.:

where CONTAINS (indexname,
 '<query>
   <textquery lang="ENGLISH" grammar="CONTEXT">
     <progression>
       <seq>(horrible WITHIN Column1) AND (movie WITHIN CategoryColumn)</seq>
       <seq>(horrible WITHIN Column2) AND (movie WITHIN CategoryColumn)</seq>
     </progression>
   </textquery>
   <score datatype="INTEGER" algorithm="COUNT"/>
</query>')>0;

This way you don't need to assign weights, as scoring from the more relaxed query never exceeds the previous one in sequence.

Tomasz
  • 5,269
  • 8
  • 56
  • 65