Relevancy using Oracle Text

Question

I want to implement a relevancy based text search on News text. Here is the requirement.

A table will contain the news text in an Oracle table column of type CLOB. The search will retrieve the news article based on relevancy of the user entered value in descending order. e.g. user enters a search text , "grand slam tournament". The application will return all news articles which has one or more of these 3 keywords in this order,

1. All articles with "grand slam tournament" in it
2. All articles which has either "grand slam" or "slam tournament"
3. All articles which has all 3 words in it but not contiguous
4. All articles which has any 2 of these words in it but none contiguous
5. All articles which has at least one of these words in it

LIKE search will be slow due to high data volume and long text

Can this be implemented using Oracle TEXT search features of Oracle 11g ?

Answer 1

This may be somewhat offtopic, but depending on your environment (and if you are not locked purely to the database), we have found that using something like a SOLR server (or lucene app like hibernate-search) performs better, give better relevancy and is more configurable than most DB's.

In my experience, we got mysql to do this using the full text search feature, but it never really worked the way we wanted to.

Answer 2

While I'm not great with oracle but to the best of my knowledge the text data type requires the use of contains keyword and text data types should be indexed to to provide improved search performance

See oracle docs for usage of the new full text search

Answer 3

Check out the following example and it will explain how to search using "Contains".

http://www.oracle-base.com/articles/9i/FullTextIndexingUsingOracleText9i.php

This document also includes the operators that you can use within the contains search. http://download.oracle.com/docs/cd/B28359_01/text.111/b28304/cqoper.htm#autoId8