Quick Text Count

Ever wondered how a search engine gives you such a quick answer: 5000 pages match your search term....

Here's how to do it in Oracle using Oracle Text looking for the words my and search.

declare
lcount number;
begin
 lcount := ctx_query.count_hits(index_name => 'IDX_IM_SEARCHIDX',
                                text_query => 'my and search',                        
                                exact => false);
dbms_output.put_line('Number of matching docs '||lcount);
end;

The call to count_hits can be adjusted for accuracy. (exact => true) takes longer to complete but is accurate while (exact => false) gives a best guess, usually very close anyway.

Note no column was queried. The index was hit directly and assuming there is no thesaurus or fuzzy matching taking place results are extremely fast.