Querying an index

The table function created by CREATEINDEX has the following shape:

$SCHEMANAME.$TABLENAME__TEXTCOL
(
    QUERY VARCHAR( 32672 ),
    WINDOWSIZE INT,
    SCORECEILING REAL
)
RETURNS TABLE
(
    $keyColumn1 $keyColumn1datatype,
    ...
    $keyColumnN $keyColumnNdatatype,
    DOCUMENTID INT,
    SCORE REAL
)

The arguments have the following meaning:

• QUERY: This is the Lucene query text. For more information, see the description of the Lucene query language.
• WINDOWSIZE: This is the maximum number of rows (matches) to return.
• SCORECEILING: This causes Lucene to return only rows whose score is less than this number. WINDOWSIZE and SCORECEILING are the variables which Lucene uses to process a result into windows. See the example below. A value of NULL means "return the best WINDOWSIZE matches".

Remember that when the index was created, the application specified how the query should be parsed.

In the returned result set, the key columns join back to the original table or view, and they identify which row of that table/view holds the scored text. The other columns in the returned result set have the following meanings:

• DOCUMENTID: This is a Lucene-generated number which may be useful for debugging Lucene-related issues. This number has no meaning to Derby or to the end user.
• SCORE: This value measures how well Lucene thought the column fit the query. A higher score means a better fit.

Derby uses the same analyzer to query the index that was last used to create or update the index.

Example

-- Selects the primary key and score for the best 3 matches for
-- the text.
select presidentID, speechID, score
from table
(
    us.presidentsSpeeches__speechText
    (
        'When in the course of human events',
        3,
        null
    )
) t;

-- The last row in the previous result had score 1.0.
-- This selects the primary key and score for the next 4 matches for the
-- text.
select presidentID, speechID, score
from table
(
    us.presidentsSpeeches__speechText
    (
        'When in the course of human events',
        4,
        1.0
    )
) t;