Using the RESULTLIMIT option in the CONTAINS and SCORE functions

The purpose of using the RESULTLIMIT option in the third parameter of the CONTAINS and SCORE function is to optimize performance.

About this task

Use of this option is effective only if the Db2 optimizer chooses a plan to retrieve a set of results from the text search server before joining those results with the results from other parts of the SQL search condition, which is called table mode for the text search query.

Example

The examples in this section use the RESULTLIMIT option.

Example 1: The following example uses the table mode and returns 5 results at the most: 
select myColumn from myTable where contains(myColumn, ‘myQuery', 
‘RESULTLIMIT=5') =1;
Example 2: The following example does not use the table mode. This query asks the text search server for every row in the table, if it matches the query (called "scalar mode" for the text search query). Therefore, the RESULTLIMIT option does not reduce the number of rows that are returned. 
select contains(myColumn, ‘myQuery', ‘RESULTLIMIT=5') from myTable;

Example 3: For more complex queries it might not be obvious if the Db2 optimizer chooses the table or scalar search mode. Therefore, you should know the plan for the SQL statement with text search functions before using the RESULTLIMIT option to avoid unexpected results. You might use IBM® OMEGAMON® for Db2 Performance Expert on z/OS® for this task.

Consider the following query:
select myColumn from myTable 
where score(myColumn, ‘myQuery')>0.4 and    
           score(myColumn, ‘myQuery', ‘RESULTLIMIT=1')<0.6 ;
If the Db2 optimizer chooses to evaluate the first score predicate in table mode, and the second in scalar mode, the specified RESULTLIMIT value would not be effective. When you exchange the two score predicates, the evaluation order might change, providing you with a different number of results.
Example 4: In the following example, the query returns both the row and score values for the first 10 results that contain the keyword “fence:” 
SELECT C2, SCORE(C2, 'fence') FROM T1 WHERE CONTAINS(C2, 'fence', 
'RESULTLIMIT=10')=1;
For this query, the Db2 optimizer creates a table function on CONTAINS(C2, 'fence', 'RESULTLIMIT=10') and then performs a scalar search on the corresponding rows again for SCORE(C2, 'fence'). Therefore, this query results in multiple calls to the server. In Db2, if the first, second, and third parameters are identical, the query is optimized to perform both the CONTAINS and SCORE functions at the same time. To improve performance, if the first and second parameters are the same for both the CONTAINS and SCORE functions, make sure that the third parameter is identical as well (character for character).
Consider the following query, which has been modified so that the first, second, and third parameter are the same: 
SELECT C2, SCORE(C2, 'fence', 'RESULTLIMIT=10') FROM T1 
WHERE CONTAINS(C2, 'fence', 'RESULTLIMIT=10')=1;
In this example, the Db2 optimizer recognizes that the CONTAINS and SCORE functions are the same function and only does a table function on CONTAINS(C2, 'fence', 'RESULTLIMIT=10'), while at the same time returning the SCORE results. Even though in this example the 'RESULTLIMIT=10' is not used in the SELECT clause for the SCORE function, including this option helps to improve performance.