3 Oracle Text CONTAINS Query Operators

General Behavior

Use the ABOUT operator to return documents that are related to a query term or phrase. In English and French, ABOUT enables you to query on concepts, even if a concept is not actually part of a query. For example, an ABOUT query on heat might return documents related to temperature, even though the term temperature is not part of the query.

In other languages, using ABOUT will often increase the number of returned documents and may improve the sorting order of results. For all languages, Oracle Text scores results for an ABOUT query with the most relevant document receiving the highest score.

English and French Behavior

In English and French, use the ABOUT operator to query on concepts. The system looks up concept information in the theme component of the index. Create a theme component to your index by setting the INDEX_THEMES BASIC_LEXER attribute to YES.

Oracle Text retrieves documents that contain concepts that are related to your query word or phrase. For example, if you enter an ABOUT query on California, the system might return documents that contain the terms Los Angeles and San Francisco, which are cities in California.The document need not contain the term California to be returned in this ABOUT query.

The word or phrase specified in your ABOUT query need not exactly match the themes stored in the index. Oracle Text normalizes the word or phrase before performing lookup in the index.

You can use the ABOUT operator with the CONTAINS and CATSEARCH SQL operators. In the case of CATSEARCH, you must use query templating with the CONTEXT grammar to query on the indexed themes. See ABOUT Query with CATSEARCH in the Examples section.

Syntax

Case-Sensitivity

ABOUT queries give the best results when your query is formulated with proper case. This is because the normalization of your query is based on the knowledge catalog which is case-sensitive.

However, you need not type your query in exact case to obtain results from an ABOUT query. The system does its best to interpret your query. For example, if you enter a query of CISCO and the system does not find this in the knowledge catalog, the system might use Cisco as a related concept for look-up.

Improving ABOUT Results

The ABOUT operator uses the supplied knowledge base in English and French to interpret the phrase you enter. Your ABOUT query therefore is limited to knowing and interpreting the concepts in the knowledge base.

Improve the results of your ABOUT queries by adding your application-specific terminology to the knowledge base.

Limitations

The phrase you specify in an ABOUT query cannot be more than 4000 characters.

Examples

Single Words

To search for documents that are about soccer, use the following syntax:

'about(soccer)'

Phrases

Further refine the query to include documents about soccer rules in international competition by entering the phrase as the query term:

'about(soccer rules in international competition)'

In this English example, Oracle Text returns all documents that have themes of soccer, rules, or international competition.

In terms of scoring, documents which have all three themes will generally score higher than documents that have only one or two of the themes.

Unstructured Phrases

You can also query on unstructured phrases, such as the following:

'about(japanese banking investments in indonesia)'

Combined Queries

Use other operators, such as AND or NOT, to combine ABOUT queries with word queries. For example, enter the following combined ABOUT and word query:

'about(dogs) and cat'

Combine an ABOUT query with another ABOUT query as follows:

'about(dogs) not about(labradors)'

ABOUT Query with CATSEARCH

Enter ABOUT queries with CATSEARCH using the query template method with grammar set to CONTEXT as follows:

select pk||' ==> '||text from test
where catsearch(text,
'<query> 
  <textquery grammar="context">
     about(California)
  </textquery>
  <score datatype="integer"/>
</query>','')>0
order by pk; 

Syntax

ACCUMulate Scoring

ACCUMulate first scores documents on how many query terms a document matches. A document that matches more terms will always score higher than a document that matches fewer terms, even if the terms appear more frequently in the latter. In other words, if you search for dog ACCUM cat, you'll find that

the dog played with the cat 

scores higher than

the big dog played with the little dog while a third dog ate the dog food

Scores are divided into ranges. In a two-term ACCUM, hits that match both terms will always score between 51 and 100, whereas hits matching only one of the terms will score between 1 and 50. Likewise, for a three-term ACCUM, a hit matching one term will score between 1 and 33; a hit matching two terms will score between 34 and 66, and a hit matching all three terms will score between 67 and 100. Within these ranges, normal scoring algorithms apply.

You can assign different weights to different terms. For example, in a query of the form

soccer, Brazil*3

the term Brazil is weighted three times as heavily as soccer. Therefore, the document

people play soccer because soccer is challenging and fun 

will score lower than

Brazil is the largest nation in South America

but both documents will rank below

soccer is the national sport of Brazil

Note that a query of soccer ACCUM Brazil*3 is equivalent to soccer ACCUM Brazil ACCUM Brazil ACCUM Brazil. Because each query term Brazil is considered independent, the entire query is scored as though it has four terms, not two, and thus has four scoring ranges. The first Brazil-and-soccer example document shown above scores in the first range (1-25), the second scores in the third range (51-75), and the third scores in the fourth range (76-100). (No document scores in the second range, because any document with Brazil in it will be considered to match at least three query terms.)

Example

set serveroutput on;
DROP TABLE accumtbl;
CREATE TABLE accumtbl (id NUMBER, text VARCHAR2(4000) );
 
INSERT INTO accumtbl VALUES ( 1, 'the little dog played with the big dog 
      while the other dog ate the dog food');
INSERT INTO accumtbl values (2, 'the cat played with the dog');
 
CREATE INDEX accumtbl_idx ON accumtbl (text) indextype is ctxsys.context;
  
PROMPT dog ACCUM cat
SELECT SCORE(10) FROM accumtbl WHERE CONTAINS (text, 'dog ACCUM cat', 10)
   > 0;
 
PROMPT dog*3 ACCUM cat
SELECT SCORE(10) FROM accumtbl WHERE CONTAINS (text, 'dog*3 ACCUM cat', 10)
   > 0;

This produces the following output. Note that the document with both dog and cat scores highest.

dog ACCUM cat
   ID  SCORE(10)
----- ----------
    1          6
    2         52
 
dog*3 ACCUM cat
   ID  SCORE(10)
----- ----------
    1         53
    2         76

Related Topics

See also weight (*)

Syntax

Example

To obtain all the documents that contain the terms blue and black and red, enter the following query:

'blue & black & red'

In an AND query, the score returned is the score of the lowest query term. In this example, if the three individual scores for the terms blue, black, and red is 10, 20 and 30 within a document, the document scores 10.

Related Topics

Syntax

term

Specify the operand for the broader term operator. Oracle Text expands term to include the broader term entries defined for the term in the thesaurus specified by thes. For example, if you specify BTG(dog), the expansion includes only those terms that are defined as broader term generic for dog. You cannot specify expansion operators in the term argument.

The number of broader terms included in the expansion is determined by the value for level.

qualifier

Specify a qualifier for term, if term is a homograph (word or phrase with multiple meanings, but the same spelling) that appears in two or more nodes in the same hierarchy branch of thes.

If a qualifier is not specified for a homograph in a broader term query, the query expands to include the broader terms of all the homographic terms.

level

Specify the number of levels traversed in the thesaurus hierarchy to return the broader terms for the specified term. For example, a level of 1 in a BT query returns the broader term entry, if one exists, for the specified term. A level of 2 returns the broader term entry for the specified term, as well as the broader term entry, if one exists, for the broader term.

The level argument is optional and has a default value of one (1). Zero or negative values for the level argument return only the original query term.

thes

Specify the name of the thesaurus used to return the expansions for the specified term. The thes argument is optional and has a default value of DEFAULT. A thesaurus named DEFAULT must exist in the thesaurus tables if you use this default value.

Note:

If you specify thes, then you must also specify level.

Examples

The following query returns all documents that contain the term tutorial or the BT term defined for tutorial in the DEFAULT thesaurus:

'BT(tutorial)'

When you specify a thesaurus name, you must also specify level as in:

'BT(tutorial, 2, mythes)'

Broader Term Operator on Homographs

If machine is a broader term for crane (building equipment) and bird is a broader term for crane (waterfowl) and no qualifier is specified for a broader term query, the query

BT(crane) 

expands to:

'{crane} or {machine} or {bird}'

If waterfowl is specified as a qualifier for crane in a broader term query, the query

BT(crane{(waterfowl)}) 

expands to the query:

'{crane} or {bird}' 

Related Topics

Browse a thesaurus using procedures in the CTX_THES package.

Syntax

DEFINEMERGE ( (  (text_query1), (text_query2), … )  , operator, merge_method )

Example

'DEFINEMERGE ( ((dog), (cat)), OR, AVG)'

'DEFINEMERGE (((dog , cat) , (blue or black)), AND, MIN )'

Queries for the expression "dog ACCUM cat" and "blue OR black," using the default scoring schemes and then using the minimum score of the two as the merged-score.

'DEFINEMERGE( ((DEFINESCORE(dog, DISCRETE)) , (cat)), AND, MAX)'

Queries for the term "dog" using the DISCRETE scoring, and for the term "cat" using the default relevant scoring, and then using the maximum score of the two as the merged-score.

Related Topic

DEFINESCORE.

Syntax

DEFINESCORE (query_term, scoring_expression)

query_term

The query term or phrase. Expressions containing the following operators are also allowed:

-	-
ABOUT	EQUIV(=)
Fuzzy	Soundex (!)
Stem ($)	Wildcards (% _)
SDATA	MDATA

Examples

'DEFINESCORE (dog, OCCURRENCE)'

Queries for the word dog, and scores each document using the occurrence score. Returns the score as integer.

'DEFINESCORE (Labradors are big dog, RELEVANCE)'

Queries for the phrase Labradors are big dogs, and scores each document using the relevance score.

'cat and DEFINESCORE (dog, IGNORE)'

Queries for the words dog and cat, using only the default relevance score of cat as the overall score of the document. Returns the score as integer.

'DEFINESCORE (dog, IGNORE)'

Queries for the word dog, and returns all documents with the word dog. The result is the same as if all documents get a score of 100. Returns the score as integer.

'DEFINESCORE (dog, ABS (100-RELEVANCE))'

Queries for the word dog, and scores each document using the absolute value of 100 minus the relevance score. Returns the score as integer.

'cat and DEFINESCORE (dog, RELEVANCE*5 - OCCURRENCE)'

Returns a syntax error: Two pre-defined components are used.

When DEFINESCORE is used with query templates, the scoring_expression overrides the values specified by the template. The following example queries for "dog" and "cat," scores "cat" using OCCURRENCE(COUNT) and scores "dog" based on RELEVANCE.

query>
      <textquery grammar="CONTEXT" lang="english">
                 DEFINESCORE(dog, RELEVANCE) and  cat
      </textquery>
      <score datatype="INTEGER" algorithm="COUNT"/>
</query>

Limitations

• If the ABOUT operator is used in query_term, the OCCURRENCE and COMPLETION scoring will not be applicable. If used, the query will return a syntax error.

• The IGNORE score cannot be used as right hand of the minus operator. If used, then a syntax error will occur.

• The COMPLETION score is only applicable if the DEFINESCORE is used with a WITHIN operator to search in zone sections, for example:

  ’DEFINESCORE (dog, COMPLETION) within zonesection'
  

  otherwise, the query will return a syntax error.

• For the left hand operand of WITHIN:

  • All nodes must use the same predefined-scoring component. (If not specified, then the predefined scoring is RELEVANCE.)

  • If the nodes use DISCRETE or COMPLETION, then only the AND and OR operator is allowed as the left hand children of WITHIN.

  • If the nodes use DISCRETE or COMPLETION, then WITHIN will use the max score of all section instances as the score.

  • If the nodes use RELEVANCE or OCCURRENCE, then WITHIN will use the summation of the score of all section instances as the score.

• Only one predefined scoring component can be used in the scoring_expression at one time. If more than one predefined scoring component is used, then a syntax error will occur.

Notes

• The DEFINESCORE operator, the absolute function, the logarithmic function, and the pre-defined scoring components are case-insensitive.

• The query_term and the scoring_expression parameters are mandatory.

• The final score of the DEFINESCORE operator will be truncated to be in the 0 – 100 range. If the data type is INTEGER, then the score is rounded up.

• The intermediate data type of the scoring value is a double precision float. As a result, the value is limited to be in the -3.4e38 to 3.4e38 range. If the intermediate scoring of any document exceeds the value, then the score will be truncated. If an integer scoring is required, then the score will always be rounded up after the score is calculated.

• The DEFINESCORE operator can be used as an operand of the following operators:

  • AND

  • NOT

  • INPATH

  • THRESHOLD

  • WITHIN

  • SQE

  • OR

  • DEFINEMERGE

  • MINUS

  • WEIGHT

  • ACCUM

  For example, the following statement is valid:

  DEFINESCORE(’dog', OCCURRENCE) AND DEFINESCORE(’cat', RELEVANCE)
  

  Queries for the term "dog" using occurrence scoring, and the term "cat" using relevance scoring.

• If DEFINESCORE is used as a parameter of other operators, then an error will be returned. For example, the following example returns an error:

  SYN(DEFINESCORE(’cat', OCCURRENCE))
  

• When used with query templates, the scoring_expression overrides the values specified by the template. For example,

  query>
      <textquery grammar="CONTEXT" lang="english">
          DEFINESCORE(dog, RELEVANCE) and  cat
      </textquery>
      <score datatype="INTEGER" algorithm="COUNT"/>
  </query>
  

  Queries for "dog" and "cat", scores "cat" using OCCURRENCE(COUNT), and scores "dog" based on RELEVANCE.

Related Topic

DEFINEMERGE.

Syntax

Example

The following example returns all documents that contain either the phrase alsatians are big dogs or labradors are big dogs:

'labradors=alsatians are big dogs'

Operator Precedence

The EQUIV operator has higher precedence than all other operators except the expansion operators (fuzzy, soundex, stem).

Supported Languages

Oracle Text supports fuzzy definitions for English, French, German, Italian, Dutch, Spanish, Portuguese, Japanese, OCR, and auto-language detection.

Stopwords

If the fuzzy expansion returns a stopword, the stopword is not included in the query or highlighted by CTX_DOC.HIGHLIGHT or CTX_DOC.MARKUP.

Base-Letter Conversion

If base-letter conversion is enabled for a text column and the query expression contains a fuzzy operator, Oracle Text operates on the base-letter form of the query.

Syntax

fuzzy(term, score, numresults, weight)

Examples

Consider the CONTAINS query:

...CONTAINS(TEXT, 'fuzzy(government, 70, 6, weight)', 1) > 0;

This query expands to the first six fuzzy variations of government in the index that have a similarity score over 70.

In addition, documents in the result set are weighted according to their similarity to government. Documents containing words most similar to government receive the highest score.

Skip unnecessary parameters using the appropriate number of commas. For example:

'fuzzy(government,,,weight)'

Backward Compatibility Syntax

The old fuzzy syntax from previous releases is still supported. This syntax is as follows:

Syntax

Using Special Characters with HASPATH and INPATH

The following rules govern the use of special characters with regard to both the HASPATH and INPATH operators:

• Left-brace ({) and right-brace (}) characters are not allowed inside HASPATH or INPATH expressions unless they are inside the equality operand enclosed by double quotes. So both 'HASPATH({/A/B})' and 'HASPATH(/A/{B})' will return errors. However, 'HASPATH(/A[B="{author}"])' will be parsed correctly.

• With exception of the backslash (\), special characters, such as dollar sign ($), percent sign (%), underscore (_), left brace ({), and right brace (}), when inside the equality operand enclosed by double or single quotes, have no special meaning. (That is, no stemming, wildcard expansion, or similar processing will be performed on them.) However, they are still subject to regular text lexing and will be translated to whitespace, with the exception of characters declared as printjoins. A backslash will still escape any character that immediately follows it.

  For example, if the hyphen (-) and the double quote character (") are defined as printjoins in a lexer preference, then:

  • The string B_TEXT inside HASPATH(/A[B="B_TEXT") will be lexed as the phrase B TEXT.

  • The string B-TEXT inside HASPATH(/A[B="B-TEXT") will be lexed as the word B-TEXT.

  • The string B'TEXT inside HASPATH(/A[B="B'TEXT") will be lexed as the word B"TEXT. You must use a backslash to escape the double quote between B and TEXT, or you will get a parsing error.

  • The string {B_TEXT} inside HASPATH(/A[B="{B_TEXT}") will be lexed as a phrase B TEXT.

Example

Path Testing

The query

HASPATH(A/B/C)

finds and returns a score of 100 for the document

<A><B><C>dog</C></B></A>

without the query having to reference dog at all.

Section Equality Testing

The query

dog INPATH A

finds

<A>dog</A>

but it also finds

<A>dog park</A>

To limit the query to the term dog and nothing else, you can use a section equality test with the HASPATH operator. For example,

HASPATH(A="dog")

finds and returns a score of 100 only for the first document, and not the second.

Limitations

Because of how XML section data is recorded, false matches might occur with XML sections that are completely empty as follows:

<A><B><C></C></B><D><E></E></D></A>

A query of HASPATH(A/B/E) or HASPATH(A/D/C) falsely matches this document. This type of false matching can be avoided by inserting text between empty tags.

Syntax

The INPATH operator has the following syntax:

Top-Level Tag Searching

Any-Level Tag Searching

Direct Parentage Path Searching

Single-Level Wildcard Searching

Multi-level Wildcard Searching

Any-Level Descendant Searching

Attribute Searching

Descendant/Attribute Existence Testing

Attribute Value Testing

Tag Value Testing

Not

AND and OR Testing

Combining Path and Node Tests

Nested INPATH

Nest the entire INPATH expression in another INPATH expression as follows:

(dog INPATH (//A/B/C)) INPATH (D)

When you do so, the two INPATH paths are completely independent. The outer INPATH path does not change the context node of the inner INPATH path. For example:

(dog INPATH (A)) INPATH (D)

never finds any documents, because the inner INPATH is looking for dog within the top-level tag A, and the outer INPATH constrains that to document with top-level tag D. A document can have only one top-level tag, so this expression never finds any documents.

Case-Sensitivity

Tags and attribute names in path searching are case-sensitive. That is,

dog INPATH (A)

finds <A>dog</A> but does not find <a>dog</a>. Instead use

dog INPATH (a)

Using Special Characters with INPATH

See "Using Special Characters with HASPATH and INPATH" for information on using special characters, such as the percent sign (%) or the backslash (\), with INPATH.

Examples

Top-Level Tag Searching

To find all documents that contain the term dog in the top-level tag <A>:

dog INPATH (/A)

or

dog INPATH(A)

Any-Level Tag Searching

To find all documents that contain the term dog in the <A> tag at any level:

dog INPATH(//A)

This query finds the following documents:

<A>dog</A>

and

<C><B><A>dog</A></B></C>

Direct Parentage Searching

To find all documents that contain the term dog in a B element that is a direct child of a top-level A element:

dog INPATH(A/B)

This query finds the following XML document:

<A><B>My dog is friendly.</B><A>

but does not find:

<C><B>My dog is friendly.</B></C>

Tag Value Testing

You can test the value of tags. For example, the query:

dog INPATH(A[B="dog"])

Finds the following document:

<A><B>dog</B></A>

But does not find:

<A><B>My dog is friendly.</B></A>

Attribute Searching

You can search the content of attributes. For example, the query:

dog INPATH(//A/@B)

Finds the document

<C><A  B="snoop dog"> </A> </C>

Attribute Value Testing

You can test the value of attributes. For example, the query

California INPATH (//A[@B = "home address"])

Finds the document:

<A B="home address">San Francisco, California, USA</A>

But does not find:

<A B="work address">San Francisco, California, USA</A>

Path Testing

You can test if a path exists with the HASPATH operator. For example, the query:

HASPATH(A/B/C)

finds and returns a score of 100 for the document

<A><B><C>dog</C></B></A>

without the query having to reference dog at all.

Limitations

Testing for Equality

The following is an example of an INPATH equality test.

dog INPATH (A[@B = "foo"])

The following limitations apply for these expressions:

• Only equality and inequality are supported. Range operators and functions are not supported.

• The left hand side of the equality must be an attribute. Tags and literals here are not enabled.

• The right hand side of the equality must be a literal. Tags and attributes here are not allowed.

• The test for equality depends on your lexer settings. With the default settings, the query

  dog INPATH (A[@B= "pot of gold"])
  

  matches the following sections:

  <A B="POT OF GOLD">dog</A>
  

  and

  <A B="pot of gold">dog</A>
  

  because lexer is case-insensitive by default.

  <A B="POT IS GOLD">dog</A>
  

  because of and is are default stopwords in English, and a stopword matches any stopword word.

  <A B="POT_OF_GOLD">dog</A>
  

  because the underscore character is not a join character by default.

Syntax

sectionname

The name of the MDATA section(s) to search. MDATA will also search DATE or numerical equality if the sectionname parameter is mapped to a FILTER BY column of DATE or some numerical type.

value

The value of the MDATA section. For example, if an MDATA section called Booktype has been created, it might have a value of paperback.

For MDATA operator on MDATA sections that are mapped to a DATE FILTER BY column, the MDATA value must follow the Date format: YYYY-MM-DD HH24:MI:SS. Otherwise, the expected rows will not be returned. If the time component is omitted, it will default to 00:00:00, according to SQL semantics.

Example

Suppose you want to query for books written by the writer Nigella Lawson that contain the word summer. Assuming that an MDATA section called AUTHOR has been declared, you can query as follows:

Notes

MDATA query values ignore stopwords.

The MDATA operator returns 100 or 0, depending on whether the document is a match.

The MDATA operator is not supported for CTXCAT, CTXRULE, or CTXXPATH indexes.

Table 3-2 shows how MDATA interacts with some other query operators:

When MDATA sections repeat, each instance is a separate and independent value. For instance, the document

<AUTHOR>Terry Pratchett</AUTHOR><AUTHOR>Douglas Adams</AUTHOR>

can be found with any of the following queries:

MDATA(author, Terry Pratchett)
MDATA(author, Douglas Adams)
MDATA(author, Terry Pratchett) and MDATA(author, Douglas Adams)

but not any of the following:

MDATA(author, Terry Pratchett Douglas Adams)
MDATA(author, Terry Pratchett & Douglas Adams)
MDATA(author, Pratchett Douglas)

Related Topics

See also "ADD_MDATA" and "ADD_MDATA_SECTION", as well as the Section Searching chapter of the Oracle Text Application Developer's Guide.

Syntax

Example

Suppose a query on the term cars always returned high scoring documents about Ford cars. You can lower the scoring of the Ford documents by using the expression:

'cars - Ford'

In essence, this expression returns documents that contain the term cars and possibly Ford. However, the score for a returned document is the score of cars minus the score of Ford.

Related Topics

Syntax

Example

The children of the MNOT operator must be a TERM or PHRASE.

SELECT * FROM docs
WHERE CONTAINS(txt, 'term1 mnot term1 term2') >0

Related Topics

Syntax

term

Specify the operand for the narrower term operator. term is expanded to include the narrower term entries defined for the term in the thesaurus specified by thes. The number of narrower terms included in the expansion is determined by the value for level. You cannot specify expansion operators in the term argument.

qualifier

Specify a qualifier for term, if term is a homograph (word or phrase with multiple meanings, but the same spelling) that appears in two or more nodes in the same hierarchy branch of thes.

If a qualifier is not specified for a homograph in a narrower term query, the query expands to include all of the narrower terms of all homographic terms.

level

Specify the number of levels traversed in the thesaurus hierarchy to return the narrower terms for the specified term. For example, a level of 1 in an NT query returns all the narrower term entries, if any exist, for the specified term. A level of 2 returns all the narrower term entries for the specified term, as well as all the narrower term entries, if any exist, for each narrower term.

The level argument is optional and has a default value of one (1). Zero or negative values for the level argument return only the original query term.

thes

Specify the name of the thesaurus used to return the expansions for the specified term. The thes argument is optional and has a default value of DEFAULT. A thesaurus named DEFAULT must exist in the thesaurus tables if you use this default value.

Note:

If you specify thes, then you must also specify level.

Examples

The following query returns all documents that contain either the term cat or any of the NT terms defined for cat in the DEFAULT thesaurus:

'NT(cat)'

If you specify a thesaurus name, then you must also specify level as in:

'NT(cat, 2, mythes)'

The following query returns all documents that contain either fairy tale or any of the narrower instance terms for fairy tale as defined in the DEFAULT thesaurus:

'NTI(fairy tale)'

That is, if the terms cinderella and snow white are defined as narrower term instances for fairy tale, Oracle Text returns documents that contain fairy tale, cinderella, or snow white.

Notes

Each hierarchy in a thesaurus represents a distinct, separate branch, corresponding to the four narrower term operators. In a narrower term query, Oracle Text only expands the query using the branch corresponding to the specified narrower term operator.

Related Topics

Browse a thesaurus using procedures in the CTX_THES package.

Normalization

A lexer does not process NDATA query phrases. Users can, however, set base letter and alternate spelling attributes for a particular section group containing NDATA sections. Query case is normalized and non-character data (except for white space) is removed (for example, numerical or punctuation).

Syntax

ndata(sectionname, phrase [,order][,proximity])

Examples

An NDATA query on an indexed surname section name that matches terms in the query phrase in any order without influencing the similarity score by the proximity of the black and smith terms has the form:

SELECT entryid, SCORE(1) FROM people WHERE
CONTAINS(idx_column, 'NDATA(surname, black smith)',1)>0;

An NDATA query on an indexed surname section name that matches terms in the query phrase in any order and in which similarity scores are influenced by the proximity of the black and smith terms has the form:

SELECT entryid, SCORE(1) FROM people WHERE
CONTAINS(idx_column, 'NDATA(surname, black smith,,proximity)',1)>0;

An NDATA query on an indexed surname section name that matches terms in the query phrase in-order without influencing the similarity score by the proximity of the black and smith terms has the form:

SELECT entryid, SCORE(1) FROM people WHERE
CONTAINS(idx_column, 'NDATA(surname, black smith, order)',1)>0;

An NDATA query on an indexed surname section name that matches terms in the query phrase in-order and in which similarity scores are influenced by the proximity of the black and smith terms has the form:

SELECT entryid, SCORE(1) FROM people WHERE
CONTAINS(idx_column, 'NDATA(surname, black smith, order, proximity)',1)>0;

Notes

The NDATA query operator does not provide offset information. As such, it cannot be used as a child of WITHIN, NEAR(;), or EQUIV(=), and NDATA sections will be ignored by CTX_DOC.HIGHLIGHT, CTX_DOC.SNIPPET, and CTX_DOC.MARKUP. The NDATA operator also is not supported in the CTXCAT grammar. It can be used with other operators, including OR and query templates.

A use case of the NDATA operator may involve finding a particular entry based on an approximate spelling of a person's full-name and an estimated date-of-birth. Supposing the entries' date-of-births are stored as an SDATA section, user-defined scoring's alternate scoring template can be used to combine the scores of the full-name's NDATA section data and the date-of-birth's SDATA section data.

The name john smith is queried for the section specified by the fullname section_name. Altering the NDATA operator's score based on the closeness of the SDATA section's date-of-birth to the date 08-NOV-2005 modifies the ranking of matching documents:

<query>
   <textquery grammar="CONTEXT" lang="english">
       NDATA(fullname, john smith)
   </textquery>
   <score algorithm="COUNT" normalization_expr = 
       "doc_score-(DATE(8-NOV-2005)-sdata:dob)"/>
</query>

Syntax

word1-n

Specify the terms in the query separated by commas. The query terms can be single words or phrases and may make use of other query operators (see "NEAR with Other Operators").

max_span

Optionally specify the size of the biggest clump. The default is 100. Oracle Text returns an error if you specify a number greater than 100.

A clump is the smallest group of words in which all query terms occur. All clumps begin and end with a query term.

For near queries with two terms, max_span is the maximum distance allowed between the two terms. For example, to query on dog and cat where dog is within 6 words of cat, enter the following query:

'near((dog, cat), 6)'

order

Specify TRUE for Oracle Text to search for terms in the order you specify. The default is FALSE.

For example, to search for the words monday, tuesday, and wednesday in that order with a maximum clump size of 20, enter the following query:

'near((monday, tuesday, wednesday), 20, TRUE)'

Note:

To specify order, then you must always specify a number for max_span.

Oracle Text might return different scores for the same document when you use identical query expressions that have the order flag set differently. For example, Oracle Text might return different scores for the same document when you enter the following queries:

'near((dog, cat), 50, FALSE)'
'near((dog, cat), 50, TRUE)'

NEAR Scoring

The scoring for the NEAR operator combines frequency of the terms with proximity of terms. For each document that satisfies the query, Oracle Text returns a score between 1 and 100 that is proportional to the number of clumps in the document and inversely proportional to the average size of the clumps. This means many small clumps in a document result in higher scores, because small clumps imply closeness of terms.

The number of terms in a query also affects score. Queries with many terms, such as seven, generally need fewer clumps in a document to score 100 than do queries with few terms, such as two.

A clump is the smallest group of words in which all query terms occur. All clumps begin and end with a query term. Define clump size with the max_span parameter, as described in this section.

The size of a clump does not include the query terms themselves. So for the query NEAR((DOG, CAT), 1), dog cat will be a match, and dog ate cat will be a match, but dog sat on cat will not be a match.

NEAR with Other Operators

You can use the NEAR operator with other operators such as AND and OR. Scores are calculated in the regular way.

For example, to find all documents that contain the terms tiger, lion, and cheetah where the terms lion and tiger are within 10 words of each other, enter the following query:

'near((lion, tiger), 10) AND cheetah'

The score returned for each document is the lower score of the near operator and the term cheetah.

You can also use the equivalence operator to substitute a single term in a near query:

'near((stock crash, Japan=Korea), 20)'

This query asks for all documents that contain the phrase stock crash within twenty words of Japan or Korea.

The following NEAR syntax is now valid:

SELECT * FROM docs WHERE CONTAINS(txt, 'near((aterm1 aterm2 ... atermI 
OR bterm1 bterm2 ... btermJ
OR cterm1 cterm2 ... ctermK, dterm))') >0

There can be any number of ORs in a given NEAR child, and the OR can appear in any of the NEAR children.

The NEAR within NEAR feature allows users to use nested proximity queries. Users can execute queries such as the following:

SELECT * FROM docs 
WHERE CONTAINS(txt, 'near((near((term1, term2),5), term3), 100)')>0

This will return documents where term1, term2, and term3 are near within a 100 token window and, additionally, the tokens term1 and term2 are near within a 5 token window.

Mixing the semicolon and NEAR syntax is not supported and will throw an error. That is, the queries "near((a;b,c),3)" or "near((a,b));c" will be disallowed.

The following operators also work with NEAR and ; :

• EQUIV

• All expansion operators that produce words, phrases, or EQUIV. These include:

  • soundex

  • fuzzy

  • wildcards

  • stem

Backward Compatibility NEAR Syntax

You can write near queries using the syntax of previous Oracle Text releases. For example, to find all documents where lion occurs near tiger, write:

'lion near tiger'

or with the semi-colon as follows:

'lion;tiger'

This query is equivalent to the following query:

'near((lion, tiger), 100, FALSE)'

Highlighting with the NEAR Operator

When you use highlighting and your query contains the near operator, all occurrences of all terms in the query that satisfy the proximity requirements are highlighted. Highlighted terms can be single words or phrases.

For example, assume a document contains the following text:

Chocolate and vanilla are my favorite ice cream flavors.  I like chocolate served
in a waffle cone, and vanilla served in a cup with carmel syrup.

If the query is near((chocolate, vanilla)), 100, FALSE), the following is highlighted:

<<Chocolate>> and <<vanilla>> are my favorite ice cream flavors.  I like
<<chocolate>> served in a waffle cone, and <<vanilla>> served in a cup with
caramel syrup.

However, if the query is near((chocolate, vanilla)), 4, FALSE), only the following is highlighted:

<<Chocolate>> and <<vanilla>> are my favorite ice cream flavors.  I like
chocolate served in a waffle cone, and vanilla served in a cup with carmel syrup.

Section Searching and NEAR

Use the NEAR operator with the WITHIN operator for section searching as follows:

'near((dog, cat), 10) WITHIN Headings'

When evaluating expressions such as these, Oracle Text looks for clumps that lie entirely within the given section.

In this example, only those clumps that contain dog and cat that lie entirely within the section Headings are counted. That is, if the term dog lies within Headings and the term cat lies five words from dog, but outside of Headings, this pair of words does not satisfy the expression and is not counted.

Syntax

Examples

To obtain the documents that contain the term animals but not dogs, use the following expression:

'animals ~ dogs'

Similarly, to obtain the documents that contain the term transportation but not automobiles or trains, use the following expression:

'transportation not (automobiles or trains)'

Related Topics

Syntax

Examples

To obtain the documents that contain the term cats or the term dogs, use either of the following expressions:

'cats | dogs'
'cats OR dogs'

Scoring

In an OR query, the score returned is the score for the highest query term. In the example, if the scores for cats and dogs is 30 and 40 within a document, the document scores 40.

Related Topics

Syntax

term

Specify the operand for the preferred term operator. term is replaced by the preferred term defined for the term in the specified thesaurus. However, if no PT entries are defined for the term, term is not replaced in the query expression and term is the result of the expansion.

You cannot specify expansion operators in the term argument.

thes

Specify the name of the thesaurus used to return the expansions for the specified term. The thes argument is optional and has a default value of DEFAULT. As a result, a thesaurus named DEFAULT must exist in the thesaurus tables before using any of the thesaurus operators.

Example

The term automobile has a preferred term of car in a thesaurus. A PT query for automobile returns all documents that contain the word car. Documents that contain the word automobile are not returned.

Related Topics

Browse a thesaurus using procedures in the CTX_THES package.

Syntax

term

Specify the operand for the related term operator. term is expanded to include term and all the related entries defined for term in thes.

You cannot specify expansion operators in the term argument.

thes

Specify the name of the thesaurus used to return the expansions for the specified term. The thes argument is optional and has a default value of DEFAULT. As a result, a thesaurus named DEFAULT must exist in the thesaurus tables before using any of the thesaurus operators.

Example

The term dog has a related term of wolf. An RT query for dog returns all documents that contain the word dog and wolf.

Related Topics

Browse a thesaurus using procedures in the CTX_THES package

Syntax

sectionname

The name of the SDATA section(s) on which to search and perform the test, or check.

SDataLiteral

The value of the SDATA section. This must be either a string literal, numeric literal, or a date string.

The SDATA operator returns a score of 100 if the enclosed predicate returns TRUE, and returns 0 otherwise. In the case of a NULL value, the SDATA operator returns a score of 0 (since in SQL it would not return TRUE).

Multi-valued semantics are not defined, as multi-valued SDATA sections are not supported.

Comparison of strings is case sensitive. The BINARY collation is always used.

Note:

For the SDATA operator on SDATA sections that are mapped to a DATE FILTER BY column, the SDATA value must follow the Date format: YYYY-MM-DD or YYYY-MM-DD HH24:MI:SS. Otherwise, the expected rows will not be returned. If the time component is omitted, it will default to 00:00:00, according to SQL semantics. This Date format is always used, regardless of the setting of the NLS_DATE_FORMAT environment variable.

Examples

Suppose that you want to query for books in the fiction category that contain the word summer. Assuming that an SDATA section called CATEGORY has been declared, you can query as follows:

Restrictions

• An error is raised if the section name is not a defined SDATA section. The source of the section (for example, tag versus column) is not important.

• The syntax precludes RHS SDATA and expressions.

• SDATA operators cannot be children of WITHIN, INPATH, HASPATH, or NEAR.

• The datatype of the named SDATA section must be compatible with the literal provided (and the operator, for example, LIKE) or an error is raised.

• SDATA operators are not supported in CTXRULE query documents.

• SDATA operators have no effect on highlighting.

Notes

Oracle recommends using SDATA operators only as descendants of AND operators that also have non-SDATA children. Essentially, use SDATA operators as secondary (that is, checking or non-driving) criteria. For instance, "find documents with DOG that also have price > 5", rather than "find documents with rating > 4". Other usage may operate properly, but may not have optimal performance.

The following examples are consistent with recommended use:

dog & SDATA(foo = 5)

The SDATA is a child of an AND operator that also has non-SDATA children.

dog & (SDATA(foo = 5) | SDATA(x = 1))

Although the SDATA operators here are children of OR, they are still descendants of an AND operator with non-SDATA children.

The following examples show use that is not recommended:

SDATA(foo = 5)

Here, SDATA is the only criteria and, therefore, the driving criteria.

dog | SDATA(bar = 9)

The SDATA in this example is a child of an OR operator rather than an AND.

SDATA(foo = 5) & SDATA(bar = 7)

While both SDATA operators in this example are descendants of AND, this AND operator does not have non-SDATA children.

Related Topics

ADD_SDATA_COLUMN

ADD_SDATA_SECTION

CTX_SECTIONS in Appendix G, "Oracle Text Views"

Syntax

Example

SELECT ID, COMMENT FROM EMP_RESUME
WHERE CONTAINS (COMMENT, '!SMYTHE') > 0 ;

ID COMMENT 
-- ------------
23 Smith is a hard worker who..

Language

Soundex works best for languages that use a 7-bit character set, such as English. It can be used, with lesser effectiveness, for languages that use an 8-bit character set, such as many Western European languages.

If you have base-letter conversion specified for a text column and the query expression contains a soundex operator, then Oracle Text operates on the base-letter form of the query.

Syntax

Examples

Behavior with Stopwords

If stem returns a word designated as a stopword, the stopword is not included in the query or highlighted by CTX_QUERY.HIGHLIGHT or CTX_QUERY.MARKUP.

Related Topics

Syntax

Examples

To create an SQE named disasters, use CTX_QUERY.STORE_SQE as follows:

begin
ctx_query.store_sqe('disasters', 'hurricane or earthquake or blizzard');
end;

This stored query expression returns all documents that contain either hurricane, earthquake or blizzard.

This SQE can then be called within a query expression as follows:

SELECT SCORE(1), docid FROM news
WHERE CONTAINS(resume, 'sqe(disasters)', 1)> 0 
ORDER BY SCORE(1);

Limitations

Up to 100 stored query expressions (SQEs) can be stored in a single Text query. If a Text query has more than 100 SQEs, including nested SQEs, then the query fails and error DRG-50949 is raised.

Syntax

term

Specify the operand for the synonym operator. term is expanded to include term and all the synonyms defined for term in thes.

You cannot specify expansion operators in the term argument.

thes

Specify the name of the thesaurus used to return the expansions for the specified term. The thes argument is optional and has a default value of DEFAULT. A thesaurus named DEFAULT must exist in the thesaurus tables if you use this default value.

Examples

The following query expression returns all documents that contain the term dog or any of the synonyms defined for dog in the DEFAULT thesaurus:

'SYN(dog)'

Compound Phrases in Synonym Operator

Expansion of compound phrases for a term in a synonym query are returned as AND conjunctives.

For example, the compound phrase temperature + measurement + instruments is defined in a thesaurus as a synonym for the term thermometer. In a synonym query for thermometer, the query is expanded to:

{thermometer} OR ({temperature}&{measurement}&{instruments})

Related Topics

Browse your thesaurus using procedures in the CTX_THES package.

Syntax

Examples

At the expression level, to search for documents that contain relational databases and to return only documents that score greater than 75, use the following expression:

'relational databases > 75'

At the query term level, to select documents that have at least a score of 30 for lion and contain tiger, use the following expression:

'(lion > 30) and tiger'

Syntax

term

Specify the operand for the translation term operator. term is expanded to include all the foreign language entries defined for term in thes. You cannot specify expansion operators in the term argument.

lang

Optionally, specify which foreign language equivalents to return in the expansion. The language you specify must match the language as defined in thes. (You may specify only one language at a time.) If you omit this parameter or specify it as ALL, the system expands to use all defined foreign language terms.

thes

Optionally, specify the name of the thesaurus used to return the expansions for the specified term. The thes argument has a default value of DEFAULT. As a result, a thesaurus named DEFAULT must exist in the thesaurus tables before you can use any of the thesaurus operators.

Note:

If you specify thes, then you must also specify lang.

Examples

Consider a thesaurus MY_THES with the following entries for cat:

cat 
  SPANISH: gato 
  FRENCH:  chat 

To search for all documents that contain cat and the spanish translation of cat, enter the following query:

'tr(cat, spanish, my_thes)'

This query expands to:

'{cat}|{gato}'

Related Topics

Browse a thesaurus using procedures in the CTX_THES package.

Syntax

term

Specify the operand for this operator. term is expanded to include all the foreign language entries and synonyms defined for term in thes. You cannot specify expansion operators in the term argument.

lang

Optionally, specify which foreign language equivalents to return in the expansion. The language you specify must match the language as defined in thes. If you omit this parameter, the system expands to use all defined foreign language terms.

thes

Optionally, specify the name of the thesaurus used to return the expansions for the specified term. The thes argument has a default value of DEFAULT. As a result, a thesaurus named DEFAULT must exist in the thesaurus tables before you can use any of the thesaurus operators.

Note:

If you specify thes, then you must also specify lang.

Examples

Consider a thesaurus MY_THES with the following entries for cat:

cat 
  SPANISH: gato 
  FRENCH:  chat 
  SYN lion 
    SPANISH: leon 

To search for all documents that contain cat, the spanish equivalent of cat, the synonym of cat, and the spanish equivalent of lion, enter the following query:

'trsyn(cat, spanish, my_thes)'

This query expands to:

'{cat}|{gato}|{lion}|{leon}'

Related Topics

Browse a thesaurus using procedures in the CTX_THES package.

Syntax

term

Specify the operand for the top term operator. term is replaced by the top term defined for the term in the specified thesaurus. However, if no TT entries are defined for term, term is not replaced in the query expression and term is the result of the expansion.

You cannot specify expansion operators in the term argument.

thes

Specify the name of the thesaurus used to return the expansions for the specified term. The thes argument is optional and has a default value of DEFAULT. A thesaurus named DEFAULT must exist in the thesaurus tables if you use this default value.

Example

The term dog has a top term of animal in the standard hierarchy of a thesaurus. A TT query for dog returns all documents that contain the phrase animal. Documents that contain the word dog are not returned.

Related Topics

Browse your thesaurus using procedures in the CTX_THES package.

Syntax

Examples

Suppose you have a collection of sports articles. You are interested in the articles about Brazilian soccer. It turns out that a regular query on soccer or Brazil returns many high ranking articles on US soccer. To raise the ranking of the articles on Brazilian soccer, enter the following query:

'soccer or Brazil*3'

Table 3-3 illustrates how the weight operator can change the ranking of three hypothetical documents A, B, and C, which all contain information about soccer. The columns in the table show the total score of four different query expressions on the three documents.

The score in the third column containing the query soccer or Brazil is the score of the highest scoring term. The score in the fourth column containing the query soccer or Brazil*3 is the larger of the score of the first column soccer and of the score Brazil multiplied by three, Brazil*3.

With the initial query of soccer or Brazil, the documents are ranked in the order C B A. With the query of soccer or Brazil*3, the documents are ranked B C A, which is the preferred ranking.

Weights can be added to multiple terms. The query Brazil OR (soccer AND Brazil)*3 will increase the relative scores for documents that contain both soccer and Brazil.

Right-Truncated Queries

Right truncation involves placing the wildcard on the right-hand-side of the search string.

For example, the following query expression finds all terms beginning with the pattern scal:

'scal%'

Left- and Double-Truncated Queries

Left truncation involves placing the wildcard on the left-hand-side of the search string.

To find words such as king, wing or sing, write the query as follows:

'_ing'

For all words that end with ing, enter:

'%ing'

Combine left-truncated and right-truncated searches to create double-truncated searches. The following query finds all documents that contain words that contain the substring %benz%

'%benz%'

Improving Wildcard Query Performance

Improve wildcard query performance by adding a substring or prefix index.

When your wildcard queries are left- and double-truncated, you can improve query performance by creating a substring index. Substring indexes improve query performance for all types of left-truncated wildcard searches such as %ed, _ing, or %benz%.

When your wildcard queries are right-truncated, you can improve performance by creating a prefix index. A prefix index improves query performance for wildcard searches such as to%.

Syntax

WITHIN Limitations

The WITHIN operator has the following limitations:

• You cannot embed the WITHIN clause in a phrase. For example, you cannot write: term1 WITHIN section term2

• Because WITHIN is a reserved word, you must escape the word with braces to search on it.

WITHIN Operator Examples

Querying Within Zone Sections

Notes

Section Names

The WITHIN operator requires you to know the name of the section you search. A list of defined sections can be obtained using the CTX_SECTIONS or CTX_USER_SECTIONS views.

Section Boundaries

For special and zone sections, the terms of the query must be fully enclosed in a particular occurrence of the section for the document to satisfy the query. This is not a requirement for field sections.

For example, consider the query where bold is a zone section:

'(dog and cat) WITHIN bold'

This query finds:

<B>dog cat</B>

but it does not find:

<B>dog</B><B>cat</B>

This is because dog and cat must be in the same bold section.

This behavior is especially useful for special sections, where

'(dog and cat) WITHIN sentence'

means find dog and cat within the same sentence.

Field sections on the other hand are meant for non-repeating, embedded metadata such as a title section. Queries within field sections cannot distinguish between occurrences. All occurrences of a field section are considered to be parts of a single section. For example, the query:

(dog and cat) WITHIN title

can find a document like this:

<TITLE>dog</TITLE><TITLE>cat</TITLE>

In return for this field section limitation and for the overlap and nesting limitations, field section queries are generally faster than zone section queries, especially if the section occurs in every document, or if the search term is common.