Re: Need Help For Search Engine...

From: Michael O'Shea <michael.oshea_at_tessella.com>
Date: 26 Feb 2005 03:13:22 -0800
Message-ID: <1109416402.660297.64400@l41g2000cwc.googlegroups.com>

Hi, your posting is a little vague on the detail such as Oracle version and functionality required. To point to the appropriate documentation resource, I suggest you look at the Oracle docs for Intermedia/Oracle text.

However I have included a sample script below to put to on the right track. The script trace demonstrates some of the more simple functionality available and will hopefully assist you in determining whether this is the appropriate technology for your requirement.

Regards
Mike

TESSELLA Michael.OShea_at_tessella.com

__/__/__/  Tessella Support Services plc
__/__/__/  3 Vineyard Chambers, ABINGDON, OX14 3PX, England
__/__/__/  Tel: (44)(0)1235-555511  Fax: (44)(0)1235-553301

www.tessella.com Registered in England No. 1466429

SQL>
SQL>
SQL> CREATE TABLE tblYourStuff(id NUMBER PRIMARY KEY,doc2search

VARCHAR2(100)); Table created.

SQL> INSERT INTO tblYourStuff(id,doc2search) 2 VALUES(1,'Oracle Queries and Solutions');

1 row created.

SQL> INSERT INTO tblYourStuff(id,doc2search) 2 VALUES(2,'The column doc2search can also be clob blob etc');

1 row created.

SQL> INSERT INTO tblYourStuff(id,doc2search) 2 VALUES(3,'and contain Adobe files, Word files, XML etc');

1 row created.

SQL> INSERT INTO tblYourStuff(id,doc2search) 2 VALUES(4,'The Oracle books are a good place to start for more detail');

1 row created.

SQL> INSERT INTO tblYourStuff(id,doc2search) 2 VALUES(5,'Read up on the LEXER and STOPWORDS too because');

1 row created.

SQL> INSERT INTO tblYourStuff(id,doc2search) 2 VALUES(6,'the text may not be indexed as you intuitively think');

1 row created.

SQL> INSERT INTO tblYourStuff(id,doc2search) 2 VALUES(7,'look for keywords near, progressions, score, ');

1 row created.

SQL> INSERT INTO tblYourStuff(id,doc2search) 2 VALUES(8,'for hit relevance/scoring and word proximity');

1 row created.

SQL>
SQL>
SQL> CREATE INDEX idxYourStuff
  2    ON tblYourStuff(doc2search)
  3     INDEXTYPE IS CTXSYS.CONTEXT
  4      PARAMETERS('SYNC(ON COMMIT)');

Index created.

SQL>
SQL> COMMIT; Commit complete.

SQL>
SQL>
SQL> --All your words (note the search word order)
SQL> SELECT id

2 FROM tblYourStuff
3 WHERE CONTAINS(doc2search,'<query><textquery grammar="context">oracle AND queries AND solutions</textquery></query>')>0;

SQL>
SQL> --All your words (note the serch word order)
SQL> SELECT id

2 FROM tblYourStuff
3 WHERE CONTAINS(doc2search,'<query><textquery grammar="context">oracle AND solutions AND queries</textquery></query>')>0;

SQL>
SQL> --One of your words
SQL> SELECT id

2 FROM tblYourStuff
3 WHERE CONTAINS(doc2search,'<query><textquery grammar="context">oracle</textquery></query>')>0;

1
4

SQL>
SQL> --Hits with any of the words
SQL> SELECT id

2 FROM tblYourStuff
3 WHERE CONTAINS(doc2search,'<query><textquery grammar="context">oracle|lexer</textquery></query>')>0;

   1
   4
   5

SQL>
SQL>
SQL> SELECT *

2 FROM V$VERSION; BANNER

Personal Oracle Database 10g Release 10.1.0.2.0 - Production PL/SQL Release 10.1.0.2.0 - Production
CORE 10.1.0.2.0 Production
TNS for 32-bit Windows: Version 10.1.0.2.0 - Production NLSRTL Version 10.1.0.2.0 - Production

SQL> SPOOL OFF Original Message: Ariji Chatterjee <arijitchatterjee123_at_yahoo.co.in> Date: 26 February 2005 09:32
Newsgroup: comp.databases.oracle.server
Subject: Need Help For Search Engine...

Dear Faculties,
I am going to incorporate a search engine in oracle with VB front end.Now I need a guidance...

Scenario:

If I am giving the search for "Oracle" then I need to use the Like Clause in filter portion.But If I serach for "Oracle Queries and Solutions" then the filter section will start to search for continuous field "Oracle Queries and Solutions".
But I want to search each word separately like google search engine.Please give some guidelines,how to move.

Thanks in advance..
Regards
Arijit Chatterjee Received on Sat Feb 26 2005 - 05:13:22 CST