efficiently find subset of records as well as total count

Question

I'm writing a function in ColdFusion that returns the first couple of records that match the user's input, as well as the total count of matching records in the entire database. The function will be used to feed an autocomplete, so speed/efficiency are its top concerns. For example, if the function receives input "bl", it might return {sampleMatches:["blue", "blade", "blunt"], totalMatches:5000}

I attempted to do this in a single query for speed purposes, and ended up with something that looked like this:

select record, count(*) over ()
from table
where criteria like :criteria
and rownum <= :desiredCount

The problem with this solution is that count(*) over () always returns the value of :desiredCount. I saw a similar question to mine here, but my app will not have permissions to create a temp table. So is there a way to solve my problem in one query? Is there a better way to solve it? Thanks!

Answer 1

I'm writing this on top of my head, so you should definitely have to time this, but I believe that using following CTE

• only requires you to write the conditions once
• only returns the amount of records you specify
• has the correct total count added to each record
• and is evaluated only once

SQL Statement

WITH q AS (
  SELECT record
  FROM   table
  WHERE  criteria like :criteria
)
SELECT q1.*, q2.*
FROM   q q1
       CROSS JOIN (
         SELECT COUNT(*) FROM q
       ) q2
WHERE  rownum <= :desiredCount

Answer 2

A nested subquery should return the results you want

select record, cnt
  from (select record, count(*) over () cnt
          from table
         where criteria like :criteria) 
 where rownum <= :desiredCount

This will, however, force Oracle to completely process the query in order to generate the accurate count. This seems unlikely to be what you want if you're trying to do an autocomplete particularly when Oracle may decide that it would be more efficient to do a table scan on table if :criteria is just b since that predicate isn't selective enough. Are you really sure that you need a completely accurate count of the number of results? Are you sure that your table is small enough/ your system is fast enough/ your predicates are selective enough for that to be a requirement that you could realistically meet? Would it be possible to return a less-expensive (but less-accurate) estimate of the number of rows? Or to limit the count to something smaller (say, 100) and have the UI display something like "and 100+ more results"?