|Oracle Text Reference
Part Number A90121-01
SQL Statements and Operators, 4 of 8
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.
Specify the text column to be searched on. This column must have a Text index associated with it.
Specify the query expression that defines your search in column.
Chapter 3, "CONTAINS Query Operators" for more information about the operators you can use in query expressions.
Optionally specify the label that identifies the score generated by the CONTAINS operator.
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.
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:
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:
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.
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;
Syntax for CONTEXT Indextype in this chapter