Re: "google search" using Oracle Text

From: jaromir nemec <jaromir_at_db-nemec.com>
Date: Mon, 19 Apr 2004 23:19:03 +0200
Message-ID: <027301c42653$f16a8ed0$2600000a@JARAWIN>

Ivan,

>Jaromir, unless I'm missing something here, with OR, I would still need to
>build my contains clause.

It is always dangerous to simple pass thru the user input in the contains condition. Let me illustrate this on an example.

TEXTID TEXT

---------- --------------------------------------------------
         8 RAC - Real Application Clusters
         9 RAC Concepts and Administration
        10 Oracle9i DBA Administration
        11 Concepts manual
        12 Clustered Database Platform - RAC
        13 RAC Concepts and OTHER TOPICS and Administration
        14 RAC and Concepts and OTHER TOPICS and Administration
        14 RAC Administration

As you can see I added some rows to make it more interesting.

First, you must distinguish a quoted search and a non quoted search:

SQL> select textid, text,score(1) from texttable where contains (text,'RAC Concepts and Administration',1)>1;

TEXTID TEXT
SCORE(1)

---------- ------------------------------------------------------- ---------
-
        13 RAC Concepts and OTHER TOPICS and Administration
4
         9 RAC Concepts and Administration

SQL> select textid, text,score(1) from texttable where contains (text,'{RAC Concepts and Administration}',1)>1;

TEXTID TEXT
SCORE(1)

---------- ------------------------------------------------------- ---------
-
         9 RAC Concepts and Administration

SQL> You see that to make quoted search (analogy to google "RAC Concepts and Administration") you must use in Oracle text {RAC Concepts and Administration}
Otherwise is will be the AND in the term interpreted as an operator!

Second, you may ACCUMulate the result of more that one search term. It is similar to OR, but there is a difference in scoring. I guess it is similar to that what you are looking for.
See Oracle Text Manual - ACCUMulate.

SQL> select textid, text,score(1) from texttable where 2 contains (text,'{RAC Concepts and Administration}, RAC , Concepts , Administration',1)>1
3 order by 3 desc;

TEXTID TEXT
SCORE(1)

---------- ------------------------------------------------------- ---------
-
         9 RAC Concepts and Administration
76
        14 RAC and Concepts and OTHER TOPICS and Administration
51
        13 RAC Concepts and OTHER TOPICS and Administration
51
        14 RAC Administration

But you must parse the search string carefully, for example generating

contains (text,'{RAC Concepts and Administration}, RAC , Concepts , and , Administration',1)>1

you will get problems (ORA-29902).

The same problem you get, if your user will try to search for "near real time" (NEAR is an operator).

regards

Jaromir D.B. Nemec

Please see the official ORACLE-L FAQ: http://www.orafaq.com

To unsubscribe send email to: oracle-l-request_at_freelists.org put 'unsubscribe' in the subject line.

--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------

Received on Mon Apr 19 2004 - 16:18:42 CDT