How do you add weights together in an oracle text index?

Question

I have created a multi column datastore on a table that allows me to do full text indexing on the table. What I need to be able to do is weight each column different and add the scores together.

The following query works, but is slow:

SELECT document.*, Score(1) + 2*Score(2) as Score
FROM document
WHERE (CONTAINS(documentContent, 'the_keyword', 1) > 0
OR CONTAINS(documentTitle, 'the_keyword', 2) > 0 )
ORDER BY Score DESC

After quite a bit of Googling, people have proposed the solution as:

SELECT document.*, Score(1) as Score
FROM document
WHERE CONTAINS(dummy, '(((the_keyword) within documentTitle))*2 OR ((the_keyword) within documentText)',1) > 0)
ORDER BY Score Desc

The above query is faster than its predecessor but it does not solve the actual problem. In this case, if the keyword is found in the documentTitle, it will not search the documentText (it uses the OR operator). What I really need is to ADD the two scores together so that if a keyword appears in the title AND the text it will have a higher score than if it only appears in the title.

So, how do you add the scores for weighted columns in one CONTAINS clause?

Answer 1

Instead of the OR operator, use ACCUM:

SELECT document.*, Score(1) as Score FROM document WHERE CONTAINS(dummy, '(((the_keyword) within documentTitle))*2 ACCUM ((the_keyword) within documentText)',1) > 0) ORDER BY Score Desc

Answer 2

What if you do a nested select?

select *, Score(1) + 2 * Score(2) as Score
from (
    SELECT document.*, Score(1) as Score
    FROM document
    WHERE CONTAINS(dummy, '(((the_keyword) within documentTitle))
        OR ((the_keyword) within documentText)',1) > 0)
)
ORDER BY Score Desc