SQL Statements and Operators, 4 of 8

CONTAINS

Use the CONTAINS operator in the WHERE clause of a SELECT statement to specify the query expression for a Text query.

CONTAINS returns a relevance score for every row selected. You obtain this score with the SCORE operator.

Syntax

CONTAINS(
         [schema.]column,
         text_query       VARCHAR2
         [,label            NUMBER])
RETURN NUMBER;

[schema.]column

Specify the text column to be searched on. This column must have a Text index associated with it.

text_query

Specify the query expression that defines your search in column.

See Also:

Chapter 3, "CONTAINS Query Operators" for more information about the operators you can use in query expressions.

label

Optionally specify the label that identifies the score generated by the CONTAINS operator.

Returns

For each row selected, CONTAINS returns a number between 0 and 100 that indicates how relevant the document row is to the query. The number 0 means that Oracle found no matches in the row.

Note:

You must use the SCORE operator with a label to obtain this number.

Example

The following example searches for all documents in the in the text column that contain the word oracle. The score for each row is selected with the SCORE operator using a label of 1:

SELECT SCORE(1), title from newsindex 
    WHERE CONTAINS(text, 'oracle', 1) > 0;

The CONTAINS operator must always be followed by the > 0 syntax, which specifies that the score value calculated by the CONTAINS operator must be greater than zero for the row to be selected.

When the SCORE operator is called (e.g. in a SELECT clause), the CONTAINS clause must reference the score label value as in the following example:

SELECT SCORE(1), title from newsindex 
     WHERE CONTAINS(text, 'oracle', 1) > 0 ORDER BY SCORE(1) DESC;

Notes

Querying Multi-Language Tables

With the multi-lexer preference, you can create indexes from multi-language tables.

At query time, the multi-lexer examines the session's language setting and uses the sub-lexer preference for that language to parse the query. If the language setting is not mapped, then the default lexer is used.

When the language setting is mapped, the query is parsed and run as usual. The index contains tokens from multiple languages, so such a query can return documents in several languages.

To limit your query to returning document of a given language, use a structured clause on the language column.

Query Performance Limitation with a Partitioned Index

Oracle Text supports the CONTEXT indexing and querying of a partitioned text table.

However, for optimal performance when querying a partitioned table with an ORDER BY SCORE clause, query the partition. If you query the entire table and use an ORDER BY SCORE clause, the query might not perform optimally unless you include a range predicate that can limit the query to a single partition.

For example, the following statement queries the partition p_tab4 partition directly:

select * from part_tab partition (p_tab4) where contains(b,'oracle') >0 ORDER BY 
SCORE;