CODEWITHSUNDEEP
oracle-databaseoracle12coracle19c
Matthew Moisen
Matthew Moisen

Reputation: 18289

How to efficiently and clearly check for existence of a row in Oracle?

Assume you have some large table foo that contains a column bar which has a relatively low number of values. For example, 10,000 rows where the bar column can contain just 'a', 'b', or 'c'.

What is the fastest and clearest way to check for the existence of a single row where the foo column contains an 'a' value, without causing Oracle to scan every row and instead short-circuit once it has located 1 matching row?

This way is fast and concise, but I don't think it is immediately clear from the use of rownum that your goal is to check existence as quickly as possible:

select count(1)
from foo
where bar = 'a' and rownum = 1;

This way is fast and more clear/obvious to me than the use of rownum but I still don't think it is immediately obvious, imo.

select count(1)
from dual
where exists (
    select 1
    from foo
    where bar = 'a'
)

Is there a standard, idiomatic way to check existence for a row in Oracle that is both fast and clear as to the intent of the query?

Upvotes: 1

Views: 94

Answers (1)

user5683823
user5683823

Reputation:

Your second version is obviously wrong - you can't select from a column (as you are, in the subquery).

The first version is perfectly fine (and efficient) - the rownum = 1 condition causes short-circuiting. It may not be immediately clear what the purpose of the rownum condition is; if that is a concern, include a comment to explain it. The query will return 1 as soon as the value of interest is first found in the column. (The table is still full-scanned, of course, if the value is not present; then the query will return 0.)

If you need an even faster check, you would need an index on column bar; the query may still be the same, but the runtime will scan the index rather than the table.

Upvotes: 1

Related Questions