0

Normally, to check existence in Oracle I will do:

SELECT COUNT(1)
FROM foo
WHERE bar = 'baz'

However, if the foo table contains multiple rows where bar='baz', this query needlessly scans through the entire table in order to report the total count.

How do I quickly check if a column in a table contains at least one row with a specified value, and have the query short-circuit, such that as soon as it detects that the value exists, the query will stop and return, as opposed to scanning the entire table?

Ideally I would want this query to return a scalar value, regardless of how many rows in the table contain the value.


The following solution appears to short-circuit (compared to SELECT COUNT(1), this is much faster for a large table), however it will either return 1 if the value exists, or return no rows at all if the value does not exist. It would be better if a row was always returned.

SELECT 1
FROM DUAL
WHERE EXISTS (
    SELECT 1
    FROM foo
    WHERE bar = 'baz'
)
1
1

Just combine the two:

SELECT count(*)
FROM DUAL
WHERE EXISTS (
    SELECT 1
    FROM foo
    WHERE bar = 'baz'
)
1

Beside Balazs' natural improvement of your query the following should work

select count(*)
from foo
where bar='baz'
and rownum=1
4
  • That still does a full table scan and always returns one row only anyway. Maybe you mean select count(*) from (select 1 from foo where bar='baz' and rownum=1) but even that is uncertain. Oct 1 '20 at 6:54
  • @AlbertGodfrind from Tom Kyte's article On ROWNUM and Limiting Results I would expect that processing is stopped after the rowcount=1 is reached. Why do you think that it still does a full table scan?
    – miracle173
    Oct 1 '20 at 15:22
  • Maybe instead of WHERE ROWNUM = 1 try FETCH FIRST 1 ROW ONLY Oct 3 '20 at 10:35
  • 1
    I guess it depends on how you define a full table scan. The query plan does include a full table scan, but the COUNT STOPKEY makes it stop as soon as it finds one matching row. Otherwise it needs to scan the whole table. Depending on the data distribution in the table, finding that first match may need a scan of 1/2 table ... It is an improvement in that it avoids scanning the entire table. The only way to avoid that would be to have an index on the filter column(s) ... Oct 12 '20 at 7:39

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.