What's the most efficient way to check if a record exists in Oracle?

A)

select decode(count(*), 0, 'N', 'Y') rec_exists
from (select 'X'
      from dual
      where exists (select 'X'
                    from sales
                    where sales_type = 'Accessories'));

B)

select decode(count(*), 0, 'N', 'Y') rec_exists
from (select 'X'
      from sales
      where sales_type = 'Accessories'); 

C) Something else (specify)

EDIT: It was hard to pick the "correct" answer, as the best approach depends on what you want to do after checking if the value exists, as pointed out by APC. I ended up picking the answer by RedFilter, since I had originally envisioned this check as a function by itself.

Solution 1:

select case 
            when exists (select 1 
                         from sales 
                         where sales_type = 'Accessories') 
            then 'Y' 
            else 'N' 
        end as rec_exists
from dual;

Solution 2:

What is the underlying logic you want to implement? If, for instance, you want to test for the existence of a record to determine to insert or update then a better choice would be to use MERGE instead.

If you expect the record to exist most of the time, this is probably the most efficient way of doing things (although the CASE WHEN EXISTS solution is likely to be just as efficient):

begin
    select null into dummy
    from sales
    where sales_type = 'Accessories'
    and rownum = 1;

    --  do things here when record exists
    ....        

exception
    when no_data_found then
        -- do things here when record doesn't exists
        .....
end;

You only need the ROWNUM line if SALES_TYPE is not unique. There's no point in doing a count when all you want to know is whether at least one record exists.

Solution 3:

select count(1) into existence 
   from sales where sales_type = 'Accessories' and rownum=1;

Oracle plan says that it costs 1 if seles_type column is indexed.