CONTAINS

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

The CONTAINS operator also supports database links. You can identify a remote table or materialized view by appending @dblink to the end of its name. The dblink must be a complete or partial name for a database link to the database containing the remote table or materialized view (querying of remote views is not supported).

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

The grammar for this operator is called the CONTEXT grammar. You can also use CTXCAT grammar if your application works better with simpler syntax. To do so, use the Query Template Specification in the text_query parameter as described in this section.

Note:

Both CTXCAT and the use of CTXCAT grammar as an alternative grammar for CONTEXT queries is deprecated. Instead, Oracle recommends that you use the CONTEXT indextype, which can provide all the same functionality, except that it is not transactional. Near-transactional behavior in CONTEXT can be achieved by using SYNC(ON COMMIT) or, preferably, SYNC(EVERY [time-period]) with a short time period.

CTXCAT was introduced when indexes were typically a few megabytes in size. Modern, large indexes, can be difficult to manage with CTXCAT. The addition of index sets to CTXCAT can be achieved more effectively by the use of FILTER BY and ORDER BY columns, or SDATA, or both, in the CONTEXT indextype. CTXCAT is therefore rarely an appropriate choice. Oracle recommends that you choose the more efficient CONTEXT indextype.

See Also:

Syntax

CONTAINS(
         [schema.]column,
         text_query    [VARCHAR2|CLOB]
         [,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 one of the following (limited to 4000 bytes for a VARCHAR2 or 64000 bytes for a CLOB):

Query Rewrite Template

Use this template to automatically write different versions of a query before you submit the query to Oracle Text. This is useful when you need to maximize the recall of a user query. For example, you can program your application to expand a single phrase query of ‘cat dog’ into the following queries:

{cat} {dog}
{cat} ; {dog}
{cat} AND {dog}
{cat} ACCUM {dog}

These queries are submitted as one query and results are returned with no duplication. In this example, the query returns documents that contain the phrase cat dog as well as documents in which cat is near dog, and documents that have cat and dog.

This is done with the following template:

 <query>
   <textquery lang="ENGLISH" grammar="CONTEXT"> cat dog
     <progression>
       <seq><rewrite>transform((TOKENS, "{", "}", " "))</rewrite></seq>
       <seq><rewrite>transform((TOKENS, "{", "}", " ; "))</rewrite></seq>
       <seq><rewrite>transform((TOKENS, "{", "}", " AND "))</rewrite></seq>
       <seq><rewrite>transform((TOKENS, "{", "}", " ACCUM "))</rewrite></seq>
     </progression>
   </textquery>
  <score datatype="INTEGER" algorithm="COUNT"/>
</query>

The operator TRANSFORM is used to specify the rewrite rules and has the following syntax (note that it uses double parentheses). The parameters are described in the following table.

TRANSFORM((terms, prefix, suffix, connector))

Table 8 TRANSFORM Parameters

Parameter Description
term Specifies the type of terms to be produced from the original query. Specify either TOKENS or THEMES.
prefix Specifies the literal string to be prepended to all terms.
suffix Specifies the literal string to be appended to all terms.
connector Specifies the literal string to connect all terms after applying the prefix and suffix.

Note: An error will be raised if the input Text query string specified in the Query Rewrite Template with TRANSFORM rules contains any Oracle Text query operators (such as AND, OR, or SOUNDEX). Also, any special characters (such as % or $) in the input Text query string must be preceded by an escape character, or an error is raised.

Query Result Set Descriptor Template

Use this template to take in a Result Set Descriptor. The element ctx_result_set_descriptor is added to the query template. This enables the CONTAINS query cursor to take in a group count query.

The Result Set Interface document is placed in a public variable in the ctx_query package. (ctx_query.result_set_document.)

The CONTAINS query cursor behavior remains unchanged and the Result Set Document is available right after closing the cursor

For example, the following query of kukui nut returns a result set with the following template.

<query>
   <textquery lang="ENGLISH" grammar="CONTEXT">
     <progression>
       <seq><rewrite>transform((TOKENS, "{", "}", " "))</rewrite></seq>
       <seq><rewrite>transform((TOKENS, "{", "}", " ; "))</rewrite></seq>
       <seq><rewrite>transform((TOKENS, "{", "}", " AND "))</rewrite></seq>
       <seq><rewrite>transform((TOKENS, "{", "}", " ACCUM "))</rewrite></seq>
     </progression>
   </textquery>
  <score datatype="INTEGER" algorithm="COUNT"/>
<ctx_result_set_descriptor>
 <group>
    <group_values>
       <value id="2"/>
       <value id="3"/>
       <value id="4"/>
    </group_values>
    <count/>
  </group>
</ctx_result_set_descriptor>
</query>

Query Relaxation Template

Use this template to progressively relax your query. Progressive relaxation is when you increase recall by progressively issuing less restrictive versions of a query, so that your application can return an appropriate number of hits to the user.

For example, the query of blue pen can be progressively relaxed to:

blue pen
blue NEAR pen
blue AND pen
blue ACCUM pen

This is done with the following template

<query>
   <textquery lang="ENGLISH" grammar="CONTEXT">
     <progression>
       <seq>blue pen</seq>
       <seq>blue NEAR pen</seq>
       <seq>blue AND pen</seq>
       <seq>blue ACCUM pen</seq>
     </progression>
   </textquery>
   <score datatype="INTEGER" algorithm="COUNT"/>
</query>

Alternate Grammar Template

Use this template to specify an alternate grammar, such as CONTEXT or CATSEARCH. Specifying an alternate grammar enables you to enter queries using different syntax and operators.

For example, with CATSEARCH, enter ABOUT queries using the CONTEXT grammar. Likewise with CONTAINS, enter logical queries using the simplified CATSEARCH syntax.

The phrase ‘dog cat mouse’ is interpreted as a phrase in CONTAINS. However, with CATSEARCH, this is equivalent to an AND query of ‘dog AND cat AND mouse’. Specify that CONTAINS use the alternate grammar with the following template:

<query>
  <textquery grammar="CTXCAT">dog cat mouse</textquery>
  <score datatype="integer"/>
</query>

Language Independent Template

Use this template to specify a lexer that uses user-defined symbols (or abbreviations) and does not depend on any language.

The following example specifies that the query take a list of language-independent sublexers.

<query>
      <textquery grammar="CONTEXT" lang="ENGLISH">
            Oracle
      </textquery>
      <score datatype="INTEGER" algorithm="COUNT"/>
      <sublexers>
            <sublexer_label>  SESSION_LANG   </sublexer_label>
            <sublexer_label>   MAIL   </sublexer_label>
            <sublexer_label> CALENDER </sublexer_label>
      </sublexers>
</query>

The following conditions apply:

Alternate Language Template

Use this template to specify an alternate language:

<query><textquery lang="french">bon soir</textquery></query>

Alternative Scoring Template

Use this template to specify an alternative scoring algorithm.

The following example specifies that the query use the CONTEXT grammar and return integer scores using the COUNT algorithm. This algorithm returns a score as the number of query occurrences in the document.

<query>
     <textquery grammar="CONTEXT" lang="english"> mustang
     </textquery>
     <score datatype="INTEGER" algorithm="COUNT"/>
</query>

The following example uses the normalization_expr attribute to add SDATA(price) into the score returned by the query, and uses it as the final score:

<query>
    <textquery grammar="CONTEXT" lang="english">
        DEFINESCORE(dog, RELEVANCE) and  cat
    </textquery>
    <score  algorithm="COUNT"  normalization_expr ="doc_score+ SDATA(price)"/>
</query>

The normalization_expr attribute is used only with the alternate scoring template, and is an arithmetic expression that consists of:

The normalization_expr attribute overrides the algorithm attribute. That is, if algorithm is set to COUNT, and the user also specifies normalization_expr, then the score will not be count, but the calculated score based on the normalization_expr.

If the score (either from algorithm = COUNT or normalization_expr = …) is internally calculated to be greater than 100, then it will be set to 100.

If the query relaxation template is used, the score will be further normalized in such a way that documents returned from higher sequences will always have higher scores than documents returned from sequence(s) below.

DATE Literal Restrictions

Only the minus (-) operator is allowed between date-type data (DATE literals and date-type SDATA). Using other operators will result in an error. Subtracting two date-type data will produce a number (float) that represents the difference in number of days between the two dates. For example, the following expression is allowed:

SDATA(dob) -  DATE(2005-11-08)

The following expression is not allowed:

SDATA(dob) +  DATE(2005-11-08)

The plus (+) and minus (-) operators are allowed between numeric data and date type of data. The number operand is interpreted as the number or fraction of days. For example, the following expression is allowed:

DATE(2005-11-08) + 1        = 9 NOV 2005

The following expression is not allowed:

DATE(2005-11-08)* 3          = ERROR

Template Attribute Values Table 1-9 gives the possible values for template attributes.

Table 9 Template Attribute Values

Tag Attribute Description Possible Values Meaning
grammar= Specifies the grammar of the query.

CONTEXT

CTXCAT

 The grammar of the query.
datatype= Specifies the type of number returned as score.

INTEGER

FLOAT

Returns score as integer between 0 and 100.

Returns score as its high-precision floating-point number between 0 and 100.

algorithm= Specifies the scoring algorithm to use.

DEFAULT

COUNT

Returns the default.

Returns scores as the number of occurrences in the document.
lang= Specifies the language name. Any language supported by Oracle Database. See Oracle Database Globalization Support Guide. The language name.

Template Grammar Definition

The query template interface is an XML document. Its grammar is defined with the following XML DTD:

<!DOCTYPE query [
<!ELEMENT query (textquery, score?, order?)>
<!ELEMENT textquery (#PCDATA|progression)*>
<!ELEMENT progression (seq)+>
<!ELEMENT seq (#PCDATA|rewrite)*>
<!ELEMENT rewrite (#PCDATA)>
<!ELEMENT score EMPTY>
<!ELEMENT order (orderkey+)>
<!ELEMENT orderkey (#PCDATA)>
<!ATTLIST textquery grammar (CONTEXT | CTXCAT | CTXRULE) #REQUIRED>
<!ATTLIST textquery lang CDATA #IMPLIED>
<!ATTLIST score datatype (integer | float) "integer">
<!ATTLIST score algorithm (default | count) "default">
<!ATTLIST score normalization_expr CDATA >

Values are case insensitive: integer | float, default | count, context |ctxcat .

See Also: Oracle Text CONTAINS Query Operators for more information about the operators in query expressions

label

Optionally, specifies the label that identifies the score generated by the CONTAINS operator.

Returns

For each row selected, the CONTAINS operator returns a number between 0 and 100 that indicates how relevant the document row is to the query. The number 0 means that Oracle Text 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 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 be followed by an expression such as > 0, which specifies that the score value calculated must be greater than zero for the row to be selected.

When the SCORE operator is called (for example, 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;

The following example specifies that the query be parsed using the CATSEARCH grammar:

SELECT id FROM test WHERE CONTAINS (text,
 '<query>
   <textquery lang="ENGLISH" grammar="CATSEARCH">
      cheap pokemon
   </textquery>
   <score datatype="INTEGER"/>
  </query>' ) > 0;

Grammar Template Example

The following example shows how to use the CTXCAT grammar in a CONTAINS query. The example creates a CTXCAT and a CONTEXT index on the same table, and compares the query results.

PROMPT create context and ctxcat indexes, both using theme indexing
PROMPT
create index tdrbqcq101x on test(text) indextype is ctxsys.context
parameters ('lexer theme_lexer');

create index tdrbqcq101cx on test(text) indextype is ctxsys.ctxcat
parameters ('lexer theme_lexer');

PROMPT *****  San Diego             ***********
PROMPT *****  CONTEXT grammar       ***********
PROMPT ** should be interpreted as phrase query **
select pk||' ==> '||text from test
where contains(text,'San Diego')>0
order by pk;

PROMPT *****  San Diego      ***********
PROMPT *****  CTXCAT grammar ***********
PROMPT ** should be interpreted as AND query  ***
select pk||' ==> '||text from test
where contains(text,
'<query>
  <textquery grammar="CTXCAT">San Diego</textquery>
  <score datatype="integer"/>
</query>')>0
order by pk;

PROMPT *****  Hitlist from CTXCAT index ***********
select pk||' ==> '||text from test
where catsearch(text,'San Diego','')>0
order by pk;

Alternate Scoring Query Template Example

The following query template adds price SDATA section (or SDATA filter-by column) value into the score returned by the query and uses it as the final score:

<query>
      <textquery grammar="CONTEXT" lang="english">
             DEFINESCORE(dog, RELEVANCE) and cat
      </textquery>
      <score algorithm="COUNT" normalization_expr ="doc_score+SDATA(price)"/>
</query>

Query Relaxation Template Example

The following query template defines a query relaxation sequence. The query of blue pen is entered in sequence as blue pen, then blue NEAR pen, then blue AND pen, and then blue ACCUM pen. Query hits are returned in this sequence with no duplication as long as the application requires results.

select id from docs where CONTAINS (text, '
<query>
   <textquery lang="ENGLISH" grammar="CONTEXT">
     <progression>
       <seq>blue pen</seq>
       <seq>blue NEAR pen</seq>
       <seq>blue AND pen</seq>
       <seq>blue ACCUM pen</seq>
     </progression>
   </textquery>
   <score datatype="INTEGER" algorithm="COUNT"/>
</query>')>0;

Query relaxation is most effective when your application requires the top n hits to a query, which you can obtain with the DOMAIN_INDEX_SORT or FIRST_ROWS hint, which is being deprecated, in a PL/SQL cursor.

Query Rewrite Template Example

The following template defines a query rewrite sequence. The query of kukui nut is rewritten as follows:

{kukui} {nut}

{kukui} ; {nut}

{kukui} AND {nut}

{kukui} ACCUM {nut}

select id from docs where CONTAINS (text, '
 <query>
   <textquery lang="ENGLISH" grammar="CONTEXT"> kukui nut
     <progression>
       <seq><rewrite>transform((TOKENS, "{", "}", " "))</rewrite></seq>
       <seq><rewrite>transform((TOKENS, "{", "}", " ; "))</rewrite>/seq>
       <seq><rewrite>transform((TOKENS, "{", "}", " AND "))</rewrite><seq/>
       <seq><rewrite>transform((TOKENS, "{", "}", " ACCUM "))</rewrite><seq/>
     </progression>
   </textquery>
  <score datatype="INTEGER" algorithm="COUNT"/>
</query>')>0;

Order By SDATA Sections Template Example

The following query template defines a query sequence for ordering by SDATA section values using the <order> and <orderkey> elements. The first level of ordering is done on the SDATA section price, which is sorted in the ascending order. The second and third level of ordering is done by the SDATA section pub_date and score, both of which are sorted in the descending order.

select id from docs where CONTAINS (text, '
<query>
   <textquery lang="ENGLISH" grammar="CONTEXT"> Oracle </textquery>
   <score datatype="INTEGER" algorithm="COUNT"/>
   <order>
       <orderkey> SDATA(price) ASC </orderkey>
       <orderkey> SDATA(pub_date) DESC </orderKey>
       <orderkey> Score DESC </orderkey>
   </order>
</query>', 1)>0;

The <orderkey> element value must have the following format:

<orderkey> SDATA(sdata_section_name) | score [DESC|ASC] </orderkey>

The sort order is ascending by default, if not specified as either DESC or ASC.

The <orderkey> element will be ignored in the following cases:

Notes

Querying Multilanguage Tables

With the multilexer preference, you can create indexes from multilanguage tables. At query time, the multilexer examines the session’s language setting and uses the sublexer 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 documents 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 DESC;

Limitation with Remote Execution of CONTAINS Query

Oracle Text supports the remote execution of the CONTAINS operator, but with some limitations. You can invoke the CONTAINS operator in a remote query only if the query is executed completely in the remote database. You cannot use the CONTAINS operator in a subquery of a query, which causes the query to run partly on the remote database and partly on the local database. Doing so will raise the error “ORA-00949: illegal reference to remote database.” However, CONTAINS, when invoked remotely from an inner query might run successfully sometimes if view merging is enabled and possible on this query, as in this case the query will be transformed into a single query and, hence, no error will occur.

For example, the following query is correct:

select id from remtab@rdb
where contains@rdb(text,'hello') > 0;

“Syntax for CONTEXT Index Type” Oracle Text CONTAINS Query Operators

“The CONTEXT Grammar” topic in Oracle Text Application Developer’s Guide

“SCORE”