Oracle Text Application Developer's Guide Release 9.0.1 Part Number A90122-01 |
|
Querying, 3 of 6
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.
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;
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.
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.
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)'
You can expand your queries into longer word lists with operators such as wildcard, fuzzy, stem, soundex, and thesaurus.
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.
You define and use a stored query expression as follows:
You can delete a stored query expression using REMOVE_SQE.
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);
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, |
|
Copyright © 1996-2001, Oracle Corporation. All Rights Reserved. |
|