Counting Hits

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;

See Also: Oracle Text Reference to learn more about the syntax of CTX_QUERY.COUNT_HITS