DB2 10.5 for Linux, UNIX, and Windows

Searching text search indexes using SCORE

You can use the SCORE function to find out the extent to which a document matches a search argument.

About this task

SCORE returns a double-precision floating-point number between 0 and 1 that indicates how well a document meets the search criteria. The better a document matches the query, the more relevant the score and the larger the result value.

The score is calculated dynamically based on the content of a text index collection at the time of the query and is therefore only meaningful for a non-partitioned text index.

Scoring algorithms may differ for different text index formats or query types. Note that deleted documents impact the relative value returned by SCORE until they are removed from the text search index. However, significant differences in scores would be observed only when large chunks of data have been deleted from the index.

Example

To search in the SAMPLE database for the number of employees who indicated on their resumes that they know how to program in Java™ or COBOL, you can issue the following query:

	SELECT EMPNO, INTEGER(SCORE(RESUME, 'programmer AND (java OR cobol)') * 100) 
	AS RELEVANCE FROM EMP_RESUME WHERE RESUME_FORMAT = 'ascii' 
	ORDER BY RELEVANCE DESC

However, the following query using CONTAINS is superior. The DB2® optimizer evaluates the CONTAINS predicate in the WHERE clause first and thereby avoids evaluating the SCORE function in the SELECT list for every row of the table. Note that this is possible only if the SCORE and CONTAINS arguments in the query are identical.

	SELECT EMPNO, INTEGER(SCORE(RESUME, 'programmer AND (java OR cobol)') * 100) 
	AS RELEVANCE FROM EMP_RESUME WHERE RESUME_FORMAT = 'ascii' 
	AND CONTAINS(RESUME, 'programmer AND (java OR cobol)') = 1 
	ORDER BY RELEVANCE DESC