Re: "google search" using Oracle Text
- From: "jaromir nemec" <jaromir@xxxxxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Mon, 19 Apr 2004 23:19:03 +0200
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
4
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
4
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
26
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@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
Other related posts:
