In oracle SQL is there a way to short circuit a SELECT COUNT result

user_1462479 0
Viewed 81

I am writing a reporting view in oracle. We need to know how many customers match on a given criteria so we can throw them into a bucket for further scrutiny before allowing them to create an account (because of bot created accounts or attempted fraud).

This example returns the numbers of customers called 'Bob' created today. Don't read too much into the example I'm trying to keep it simple...

SELECT COUNT(*) count_called_bob
FROM customers
WHERE name = 'Bob'
AND created = TRUNC(SYSDATE);

We will do some decisioning based on the value of count_called_bob looking for spikes indicating an unusual number of customers all with the same name.

However beyond a certain point it doesn't matter how many Bob's there are. We are going to put this customer through our most rigorous scrutiny anyway. Lets say this happens if there are more than 500 Bob's created today.

Does oracle have any functionality where it will stop counting when it reaches a certain threshold? I'm looking to optimise my SELECT COUNT queries and wondered if there was a way to short circuit the query when that was met.

I have performed an explain plan on the query in the answer given. Oracle calls this feature a 'stopkey'. This is the keyword you need to search to find out about this topic. However as is often the problem with SQL knowing the words to search for is the hardest bit...

oracle sql
2 Answers

You can use rownum to limit the number of rows. I might phrase this as a subquery for clarity (the subquery doesn't affect performance):

SELECT COUNT(*) as count_called_bob
FROM (SELECT 1
      FROM customers
      WHERE name = 'Bob' AND created = TRUNC(SYSDATE) AND rownum < 500
     ) b
user_11440350

For large volumes you can use a sample and adjust the result to match:

select count(*) * 10 as approx_bobcount
from   customers sample(10) c
where  c.name = 'Bob'
and    c.created = trunc(sysdate);

(or, count * 20 with sample 5, count * 100 with sample 1, etc.)

user_2304710
Related