Examples of SCORE operator on CATSEARCH

[spook007]

I'm trying to do do a full text search on my database using CATSEARCH. The fields I'm searching thru are ARTICLE_NAME, ARTICLE_DESCRIPTION, ARTICLE_KEYWORD.

The fields aren't huge so that's why I decided to go with a catalog search. I'm trying to get my query results to order by a hitlist. I'm trying to use SCORE to establish this hitlist. The below article mentions that SCORE operator can be used with CATSEARCH, but I can't find any examples of SCORE being used with CATSEARCH. All the examples use CONTAINS.

http://download-west.oracle.com/otndoc/oracle9i/901_doc/text.901/a90122/cdefaul7.htm#1004113

Does anyone know of a syntax I can use to accomplish this?

 

[spenglerr]

sorry, but I always used CONTAINS before when working with scores. Normally I take three scores and rank them to headline/keyword/text.

But I found this in Metalink:

-------------begin of paste ----------------------

The CTXCAT Query Language
-------------------------

Once the ctxcat index is created, you query using the operator catsearch
instead of contains:

select item_desc from auction
where catsearch(item_desc, 'oracle', null)>0;

This finds all rows in auction which have "oracle" in the item_desc text.
Like contains, the first argument is the text column, and the second is the
query string. The third argument we'll talk about below -- just ignore it
for now. There is no score label argument, because catsearch does not
provide scoring -- it is a simple boolean search.

The catsearch operator uses a much simpler query language than contains,
with the intent that web-style queries can be passed through to catsearch
without extra parsing. The query language rules are:

* Multiple words are treated as an AND.
* Vertical bar is used for OR.
* A leading plus sign on words is ignored and discarded
* A leading minus sign excludes words (must have included words)
* Double quotes delimit phrases.
* Parentheses group operations.

Or, in comparison:

catearch Query Equivalent contains query
-------------- -------------------------
A B A & B
A | B A | B
+A -B A ~ B
"A B" A B
"A B" C (A B) & C
(A B) | C (A & B) | C

AND, OR, AND-NOT, and PHRASE are the only functions currently available in the
catsearch query language. catsearch does not support any of the other
features found in the context query language such as accum, within, fuzzy,
stem, wildcard, thesaurus, near, etc.

 

[lbrechler]

Have a table with very large text field (CLOB actually)... Would like to be able to search the table and rank the results. Am trying a ctxsys index type to index the field, but when I go to search, the results are not good. I.e when I search for an exact phrase that I know is there, it's either not there, or it's 3 or 4 down on the list (under totally irrelevant results), and it always returns the same results not matter what the criteria. Searches for "test" and "blah" return the exact same result set, though none of them contain both "test" and "blah."

Is there an easier way to do this? Am I doing someting wrong? I'd be interested to hear common pitfalls, errors, or other ways around it.

Thanks in advance,
~Lindsay

By the way, my select statement is using CONTAINS, not CATSEARCH, if that matters...

 

[spook007]

Lindsay;
When you are using this type of search you have to remember that the search looks through the index table to find your results, so your index table has to be updated with any changes that you have done.

In order to do this you can run one of two scripts that come with oracle. They are...

drbgdml.sql
(when you run this one it looks like it crashed cause it just sits there, but it is simply running in the background. It detects whether or not there has been a change to the table if there has been it updates the index table to reflect the changes)

drjobdml.sql
(does the same thing, but does not stay running in the background but performs the check and stops.)

Hope this helps...

Spook007