A CONTAINS query expression can contain query operators that enable logical, proximity, thesaural, fuzzy, and wildcard searching. Querying with stored expressions is also possible. Within the query expression, you can use grouping characters to alter operator precedence. This book refers to these operators as the CONTEXT grammar.

With CONTAINS, you can also use the ABOUT query to query document themes.

With the CATSEARCH operator, you specify your query expression with the text_query argument and your optional structured criteria with the structured_query argument. The text_query argument enables you to query words and phrases. You can use logical operations, such as logical and, or, and not. This book refers to these operators as the CTXCAT grammar.

If you want to use the much richer set of operators supported by the CONTEXT grammar, you can use the query template feature with CATSEARCH.

With structured_query argument, you specify your structured criteria. You can use the following SQL operations:

• =

• <=

• >=

• >

• <

• IN

• BETWEEN

You can also use the ORDER BY clause to order your output.

Unlike CONTAINS and CATSEARCH, MATCHES does not take a query expression as input.

Instead, the MATCHES operator takes a document as input and finds all rows in a query (rule) table that match it. As such, you can use MATCHES to classify documents according to the rules they match.

Query applications sometimes parse end-user queries, interpreting a query string in one or more ways by using different operator combinations. For example, if a user enters a query of kukui nut, your application enters the {kukui nut} and {kukui or nut} queries to increase recall.

The query rewrite feature enables you to submit a single query that expands the original query into the rewritten versions. The results are returned with no duplication.

You specify your rewrite sequences with the query template feature. The rewritten versions of the query are executed efficiently with a single call to CONTAINS or CATSEARCH.

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}

The following is the query rewrite template for these transformations:

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;

The query relaxation feature enables your application to execute the most restrictive version of a query first and progressively relax the query until the required number of hits are obtained.

For example, your application searches first on green pen and then the query is relaxed to green NEAR pen to obtain more hits.

The following query template defines a query relaxation sequence. The query of green pen is entered in sequence.

{green} {pen}

{green} NEAR {pen}

{green} AND {pen}

{green} ACCUM {pen}

The following is the query relaxation template for these transformations:

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

Query hits are returned in this sequence with no duplication as long as the application needs results.

Query relaxation is most effective when your application needs the top-N hits to a query, which you can obtain with the DOMAIN_INDEX_SORT hint or in a PL/SQL cursor.

Using query templating to relax a query is more efficient than reexecuting a query.

When you use MULTI_LEXER to index a column containing documents in different languages, you can specify which language lexer to use during querying. You do so by using the lang parameter in the query template, which specifies the document-level lexer.

select id from docs where CONTAINS (text,
'<query><textquery lang="french">bon soir</textquery></query>')>0;

You can order the query results according to the content of SDATA sections by using the <order> and <orderkey> elements of the query template.

In the following example, the first level of ordering is performed on the SDATA price section, which is sorted in ascending order. The second and third level of ordering are performed by the SDATA pub_date section and score, both of which are sorted in 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;

You can use query templating to specify alternative scoring algorithms. Those algorithms help you customize how CONTAINS is scored. They also enable SDATA to be used as part of the scoring expressions. In this way, you can mathematically define the scoring expression by using not only predefined scoring components, but also SDATA components.

With alternative user-defined scoring, you can specify:

• Scoring expressions of terms by defining arithmetic expressions that define how the query should be scored, using

  • predefined scoring algorithms: DISCRETE, OCCURRENCE, RELEVANCE, and COMPLETION

  • arithmetic operations: plus, minus, multiply, divide

  • arithmetic functions: ABS(n), finding the absolute value of n ; LOG(n), finding the base-10 logarithmic value of n

  • Numeric literals

• Scoring expressions at the term level

• Terms that should not be taken into account when calculating the score

• How the score from child elements of OR and AND operators should be merged

• Use

You can also use the SDATA that stores numeric or DATETIME values to affect the final score of the document.

The following example specifies an alternative scoring algorithm:

select id from docs where CONTAINS (text,
'<query>        
 <textquery grammar="CONTEXT" lang="english"> mustang  </textquery>     
 <score datatype="float" algorithm="DEFAULT"/>     
</query>')>0

The following query templating example includes SDATA values as part of the final score:

select id from docs where CONTAINS (text,
'<query>
<textquery grammar="CONTEXT" lang="english"> mustang </textquery>
<score datatype="float" algorithm="DEFAULT" normalization_expr ="doc_score+SDATA(price)"/>
</query>')>0"

Query templating enables you to use the CONTEXT grammar with CATSEARCH queries and vice versa.

select id from docs where CONTAINS (text,
'<query> 
  <textquery grammar="CTXCAT">San Diego</textquery>
  <score datatype="integer"/>
</query>')>0;