Home » Server Options » Text & interMedia » Domain index DEFINESCORE
Domain index DEFINESCORE [message #603074] Wed, 11 December 2013 05:22 Go to next message
rameshaimhigh@gmail.com
Messages: 10
Registered: October 2013
Junior Member
I have one table with below data and it has domain index.

ID COLUMN1

1 French
2 French and business
3 Business French
4 French and economic science
5 Francais

I need to get score based on matching keyword position. I have tried this with define score. But it will return same score for all. I am not sure whether i tried the correct approach.

But i need the result like below
ID COLUMN1 score

1 French 4 (exact match will full text)
2 French and business 3 (searched keyword starting position is 1)
3 Business French 2 (searched keyword starting position is last)
4 French and economic science 3 (searched keyword starting position is 1)
5 Francais 1 (no match)

SQL:
select a.*,score(1)
from domain_index_test a
--where contains(column1,'DEFINESCORE(french,RELEVANCE)',1) > 0
WHERE CONTAINS(column1, 'DEFINESCORE ( ${French}, RELEVANCE)', 1) > 0

Re: Domain index DEFINESCORE [message #611126 is a reply to message #603074] Thu, 27 March 2014 21:53 Go to previous message
Barbara Boehmer
Messages: 9077
Registered: November 2002
Location: California, USA
Senior Member
SCOTT@orcl12c> CREATE TABLE domain_index_test
  2    (id	 NUMBER,
  3  	column1  VARCHAR2(30))
  4  /

Table created.

SCOTT@orcl12c> INSERT ALL
  2  INTO domain_index_test VALUES (1, 'French')
  3  INTO domain_index_test VALUES (2, 'French and business')
  4  INTO domain_index_test VALUES (3, 'Business French')
  5  INTO domain_index_test VALUES (4, 'French and economic science')
  6  INTO domain_index_test VALUES (5, 'Francais')
  7  SELECT * FROM DUAL
  8  /

5 rows created.

SCOTT@orcl12c> BEGIN
  2    CTX_DDL.CREATE_PREFERENCE ('test_mcds', 'MULTI_COLUMN_DATASTORE');
  3    CTX_DDL.SET_ATTRIBUTE ('test_mcds', 'COLUMNS', 'column1, ''start '' || column1 startcol');
  4  END;
  5  /

PL/SQL procedure successfully completed.

SCOTT@orcl12c> CREATE INDEX test_idx ON domain_index_test (column1)
  2  INDEXTYPE IS CTXSYS.CONTEXT
  3  PARAMETERS ('DATASTORE  test_mcds')
  4  /

Index created.

SCOTT@orcl12c> SELECT id, column1,
  2  	    DECODE
  3  	      (score,
  4  	       0, 1,
  5  	       DECODE
  6  		 (UTL_MATCH.EDIT_DISTANCE (UPPER (column1), UPPER ('French')),
  7  		  0, 4, score)) score
  8  FROM   (SELECT id, column1, MAX (score) score
  9  	     FROM   (SELECT a.*, 3 score
 10  		     FROM   domain_index_test a
 11  		     WHERE  CONTAINS (column1, 'start ' || 'French') > 0
 12  		     UNION ALL
 13  		     SELECT a.*, 2 score
 14  		     FROM   domain_index_test a
 15  		     WHERE  CONTAINS (column1, 'French') > 0
 16  		     UNION ALL
 17  		     SELECT a.*, 1 score
 18  		     FROM   domain_index_test a
 19  		     WHERE  CONTAINS (column1, 'French') = 0)
 20  	     GROUP  BY id, column1)
 21  ORDER  BY score DESC
 22  /

        ID COLUMN1                             SCORE
---------- ------------------------------ ----------
         1 French                                  4
         4 French and economic science             3
         2 French and business                     3
         3 Business French                         2
         5 Francais                                1

5 rows selected.

Previous Topic: Can you find which section_group matched in a multi_column_datastore?
Next Topic: Needs to create stop list for non English
Goto Forum:
  


Current Time: Thu Mar 28 10:40:36 CDT 2024