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 Text returns the documents that contain the theme.

Word information and theme information are combined into a single index. To enter a theme query in your index, you must include that is created by default in English and French.

Enter a theme query by 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;

Use logical operators to limit your search criteria in a number of ways. Table 7-1 describes some of these operators.

'cats AND dogs'
'cats & dogs'

'cats | dogs'
'cats OR dogs'

'animals ~ dogs'

'dogs, cats, puppies'

'German shepherds=alsatians are big dogs'

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

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

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

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

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

The NEAR operator is now modified to change how the distance is measured between phrases in NESTED NEAR.

The NEAR_ACCUM operator combines the functionality of the NEAR operator with that of the ACCUM operator. Like NEAR, it returns terms that are within a given proximity of each other; however, if one term is not found, it ranks documents according to the frequency of the occurrence of the term that is found.

The NEAR2 operator combines the functionality of PHRASE, NEAR, and AND operators. In addition, the NEAR2 operator can use position information to boost the scores of its hits. That is, if one phrase hit occurs at the beginning of a document and another at the end of the document, then a higher weight is given to the first hit as compared to the second hit.

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

Use the CTXCAT grammar in CONTAINS queries. To do so, use a query template specification in the text_query parameter of CONTAINS.

Take advantage of the CTXCAT grammar when you need an alternative and simpler query grammar.

Use the CTX_QUERY.STORE_SQE procedure to store the definition of a query without storing any results. Referencing the query with the CONTAINS SQL operator references the definition of the query. In this way, stored query expressions make it easy to define 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.

• Defining a Stored Query Expression

• SQE Example

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, if the french function 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);

A CONTAINS query optimized for response time provides a fast solution when you need the highest scoring documents from a hitlist.

The following example returns the first twenty hits as output. This example uses the FIRST_ROWS(n) hint and a cursor.

declare 
cursor c is  
  select /*+ FIRST_ROWS(20) */ title, score(1) score 
    from news where contains(txt_col, 'dog', 1) > 0 order by score(1) desc; 
begin 
  for c1 in c 
  loop 
    dbms_output.put_line(c1.score||':'||substr(c1.title,1,50)); 
    exit when c%rowcount = 21; 
  end loop; 
end; 
/

The following factors can also influence query response time:

• Collection of table statistics

• Memory allocation

• Sorting

• Presence of large object columns in your base table

• Partitioning

• Parallelism

• Number of term expansions in your query

  See Also:

  "Frequently Asked Questions About Query Performance"

Use CTX_QUERY.COUNT_HITS in PL/SQL or COUNT(*) in a SQL SELECT statement to count the number of hits returned from a query with only a CONTAINS predicate.

If you want a rough hit count, use CTX_QUERY.COUNT_HITS in estimate mode (EXACT parameter set to FALSE). With respect to response time, this is the fastest count you can get.

Use the COUNT(*) function in a SELECT statement to count the number of hits returned from a query that contains a structured predicate.

To find the number of documents that contain the word oracle, enter the query with the SQL COUNT function.

SELECT count(*) FROM news WHERE CONTAINS(text, 'oracle', 1) > 0;

To find the number of documents returned by a query with a structured predicate, use COUNT(*).

SELECT COUNT(*) FROM news WHERE CONTAINS(text, 'oracle', 1) > 0 and author = 'jones';

To find the number of documents that contain the word oracle, use COUNT_HITS.

declare count number;
begin
  count := ctx_query.count_hits(index_name => my_index, text_query => 'oracle', exact => TRUE);
 dbms_output.put_line('Number of docs with oracle:');
 dbms_output.put_line(count);
end;

Use the DEFINESCORE operator to define how the score for a term or phrase is to be calculated. The DEFINEMERGE operator defines how to merge scores of child elements of AND and OR operators. You can also use the alternative scoring template with SDATA to affect the final scoring of the document.