Re: Oracle Text. Number of hits per document.

From: Michael O'Shea <michael.oshea_at_tessella.com>
Date: 13 Jun 2005 13:47:42 -0700
Message-ID: <1118695662.741007.16040@g14g2000cwa.googlegroups.com>

Hi there. From your posted description I believe what you are wanting cannot be achieved without a kludge on Oracle 9i. The good news however is that in 10g this is a very simple query. I enclose an example script trace demonstrating the functionality.
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> CREATE TABLE tblTest(id NUMBER,sentence VARCHAR2(300));

Table created.

SQL> CREATE INDEX idxTest
  2 ON tblTest(sentence)
  3 INDEXTYPE IS CTXSYS.CONTEXT
  4 PARAMETERS('SYNC(ON COMMIT)'); Index created.

SQL>
SQL> INSERT INTO tblTest(id,sentence)
  2 VALUES(0,'You will probably file that this feature');

1 row created.

SQL> INSERT INTO tblTest(id,sentence)
  2 VALUES(1,'in Oracle Text is available in Oracle 10g');

1 row created.

SQL> INSERT INTO tblTest(id,sentence)
  2 VALUES(2,'and not in Oracle 9i.');

1 row created.

SQL> INSERT INTO tblTest(id,sentence)
  2 VALUES(3,'This sentence mentions the work Oracle Oracle Oracle Oracle 4 times.');

1 row created.

SQL> COMMIT; Commit complete.

SQL>
SQL> SELECT id,
  2 SCORE(5678) AS "CountOracle"

  3     FROM tblTest
  4      WHERE CONTAINS(sentence,'<query><textquery

grammar="context">Oracle</textquery><score datatype="integer" algorithm="count"/></query>',5678)>0;

        ID CountOracle
---------- -----------

         1           2
         2           1
         3           4

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 at\ wrote:
> Hi,
>
> I'm using Oracle 9iR2 and will shortly be replicating this system in
> Oracle 10G.
>
> I've just started using an Oracle Context index to perform text searches
> on quite a large dataset.
>
> I was wondering if it is at all possible to find out the actual number
> of times a certain word was mentioned in a document, as opposed to the
> score.
>
> I noticed that as part of the Salton Algorithm, Oracle Text needs to
> know the actual number of times a story mentions the search term. It
> seems that this information should be available somewhere in some form.
>
> My queries are normally structured and although count(*) may take a long
> time to run, it still does not give me what I want.
>
> Any ideas?
>
> Many Thanks,
>
> Kevin.
Received on Mon Jun 13 2005 - 15:47:42 CDT