Querying, 3 of 6

Query Operators for CONTAINS

With the CONTAINS operator, you can add complexity to your searches with query operators. You use the query operators in your query expression. For example, the logical operator AND allows you to search for all documents that contain two different words. The ABOUT operator allows you to search on concepts.

You can also use the WITHIN operator for section searching, the NEAR operator for proximity searches, the stem, fuzzy, and thesaural operators for expanding a query expression.

The following sections describe some of the Oracle Text operators.

See Also:

Oracle Text Reference for complete information about using query operators.

ABOUT Query

Use the ABOUT operator in English or French to query on a concept. The query string is usually a concept or theme that represents the idea to be searched on. Oracle returns the documents that contain the theme.

Word information and theme information are combined into a single index. To issue a theme query, your index must have a theme component which is created by default in English and French.

You issue a theme query using the ABOUT operator inside the query expression. For example, to retrieve all documents that are about politics, write your query as follows:

SELECT SCORE(1), title FROM news 
           WHERE CONTAINS(text, 'about(politics)', 1) > 0
           ORDER BY SCORE(1) DESC;

See Also:

Oracle Text Reference for more information about using the ABOUT operator.

Logical Operators

Logical operators such as AND or OR allow you to limit your search criteria in a number of ways. The following table describes some of these operators.

Operator	Symbol	Description	Example Expression
AND	&	Use the AND operator to search for documents that contain at least one occurrence of each of the query terms. Score returned is the minimum of the operands.	'cats AND dogs' 'cats & dogs'
OR	\|	Use the OR operator to search for documents that contain at least one occurrence of any of the query terms. Score returned is the maximum of the operands.	'cats \| dogs' 'cats OR dogs'
NOT	~	Use the NOT operator to search for documents that contain one query term and not another.	To obtain the documents that contain the term animals but not dogs, use the following expression: 'animals ~ dogs'
ACCUM	,	Use the ACCUM operator to search for documents that contain at least one occurrence of any of the query terms. The accumulate operator ranks documents according to the total term weight of a document.	The following query returns all documents that contain the terms dogs, cats and puppies giving the highest scores to the documents that contain all three terms: 'dogs, cats, puppies'
EQUIV	=	Use the EQUIV operator to specify an acceptable substitution for a word in a query.	The following example returns all documents that contain either the phrase alsatians are big dogs or German shepherds are big dogs: 'German shepherds=alsatians are big dogs'

Section Searching

Section searching is useful for when your document set is HTML or XML. For HTML, you can define sections using embedded tags and then use the WITHIN operator to search these sections.

For XML, you can have the system automatically create sections for you. You can query with the WITHIN operator or with the INPATH operator for path searching.

See Also:

Chapter 6, "Document Section Searching"

Proximity Queries with NEAR Operator

You can search for terms that are near to one another in a document with the NEAR operator.

For example, to find all documents where dog is within 6 words of cat, issue the following query:

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

See Also:

Oracle Text Reference for more information about using the NEAR operator.

Fuzzy, Stem, Soundex, Wildcard and Thesaurus Expansion Operators

You can expand your queries into longer word lists with operators such as wildcard, fuzzy, stem, soundex, and thesaurus.

See Also:

Oracle Text Reference for more information about using these operators.

Stored Query Expressions

You can use the procedure CTX_QUERY.STORE_SQE to store the definition of a query without storing any results. Referencing the query with the CONTAINS SQE operator references the definition of the query. In this way, stored query expressions make it easy for defining long or frequently used query expressions.

Stored query expressions are not attached to an index. When you call CTX_QUERY.STORE_SQE, you specify only the name of the stored query expression and the query expression.

The query definitions are stored in the Text data dictionary. Any user can reference a stored query expression.

See Also:

Oracle Text Reference to learn more about the syntax of CTX_QUERY.STORE_SQE.

Defining a Stored Query Expression

You define and use a stored query expression as follows:

Call CTX_QUERY.STORE_SQE to store the results for the text column. With STORE_SQE, you specify a name for the stored query expression and a query expression.
Call the stored query expression in a query expression using the SQE operator. Oracle returns the results of the stored query expression in the same way it returns the results of a regular query. The query is evaluated at the time the stored query expression is called.

You can delete a stored query expression using REMOVE_SQE.

SQE Example

The following example creates a stored query expression called disaster that searches for documents containing the words tornado, hurricane, or earthquake:

begin
ctx_query.store_sqe('disaster', 'tornado | hurricane | earthquake');
end;

To execute this query in an expression, write your query as follows:

SELECT SCORE(1), title from news 
   WHERE CONTAINS(text, 'SQE(disaster)', 1) > 0
   ORDER BY SCORE(1);

See Also:

Oracle Text Reference to learn more about the syntax of CTX_QUERY.STORE_SQE.

Calling PL/SQL Functions in CONTAINS

You can call user-defined functions directly in the CONTAINS clause as long as the function satisfies the requirements for being named in a SQL statement. The caller must also have EXECUTE privilege on the function.

For example, assuming the function french returns the French equivalent of an English word, you can search on the French word for cat by writing:

SELECT SCORE(1), title from news 
   WHERE CONTAINS(text, french('cat'), 1) > 0
   ORDER BY SCORE(1);

See Also:

Oracle9i SQL Reference for more information about creating user functions and calling user functions from SQL,