Counting Hits

To count the number of hits returned from a query with only a CONTAINS predicate, you can use CTX_QUERY.COUNT_HITS in PL/SQL or COUNT(*) in a SQL SELECT statement.

If you want a rough hit count, you can 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.

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

SQL Count Hits Example

To find the number of documents that contain the word oracle, issue the query with the SQL COUNT function as follows:

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

Counting Hits with a Structured Predicate

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

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

PL/SQL Count Hits Example

To find the number of documents that contain the word oracle, use COUNT_HITS as follows:

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.